distinct
1.去重:针对某一字段去重
SELECT DISTINCT name FROM TABLE
2.多字段去重:查找字段A+B组合的记录在表中不重复的记录;
SELECT DISTINCT A,B from TABLE
3.多字段查询并且单一字段去重:查询字段有多个,但是只针对一个字段去重
①group_concat 函数
SELECT GROUP_CONCAT( DISTINCT a) AS a, b FROM eams_test GROUP BY a
group_concat :
普通的group_concat函数使用
group_concat(price separator ';') 将结果以逗号分隔
②group by 分组函数
4.demo:
建测试表
CREATE TABLE eams_test
(
id
varchar(32) DEFAULT NULL,
name
varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
导入数据
INSERT INTO eams_test
VALUES (‘P001’, ‘拉布拉多’);
INSERT INTO eams_test
VALUES (‘P001’, ‘拉布拉多’);
INSERT INTO eams_test
VALUES (‘P001’, ‘拉跨’);
INSERT INTO eams_test
VALUES (‘P003’, ‘斯巴达’);
INSERT INTO eams_test
VALUES (‘P001’, ‘飘洋过海’);
INSERT INTO eams_test
VALUES (‘P001’, ‘挪威的森林’);
INSERT INTO eams_test
VALUES (‘P001’, ‘拉布拉多’);
INSERT INTO eams_test
VALUES (‘P004’, ‘炎日’);
INSERT INTO eams_test
VALUES (‘P001’, ‘费尔特’);
INSERT INTO eams_test
VALUES (‘P002’, ‘左将军威武’);
INSERT INTO eams_test
VALUES (‘P003’, ‘寂静’);
INSERT INTO eams_test
VALUES (‘P002’, ‘左将军威武’);