SQL 去除重複 不同[DISTINCT] / 分群[GROUP BY] 和 DISTINCT vs GROUP BY 效率

 

資料來源:http://tc.wangchao.net.cn/bbs/detail_1846934.html
http://www.111cn.net/database/mysql/71592.htm

 

如果多欄位 要使用不同[DISTINCT]去除重複 就要把DISTINCT欄位放在第一個

 

如果使用 分群[GROUP BY] 去除重複 要放在WHERE 後面

 

PS 分群效率比較好 (資料來源二的說法)

EX:

CREATE TABLE IF NOT EXISTS `door_group_detail` (
  `door_group_id` int(11) NOT NULL,
  `area_id` int(11) DEFAULT NULL,
  `door_id` int(11) DEFAULT NULL,
  `floor_id` int(11) DEFAULT NULL,
  `state` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `unit` int(11) NOT NULL DEFAULT ‘0’,
  `descript` text,
  `state` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

————————————————————-

SELECT DISTINCT d_g_d.door_group_id AS unit,a.name AS name,a.id AS id

FROM area AS a,door_group_detail AS d_g_d

WHERE a.id=d_g_d.area_id;

或者

SELECT a.name AS name, a.id AS id, d_g_d.door_group_id AS unit

FROM area AS a, door_group_detail AS d_g_d

WHERE a.id = d_g_d.area_id

GROUP BY d_g_d.door_group_id;