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 :

MySQL对两个字段去重 mysql根据多个字段去重_字段

普通的group_concat函数使用

MySQL对两个字段去重 mysql根据多个字段去重_字段_02

group_concat(price separator ';')  将结果以逗号分隔

②group by 分组函数

MySQL对两个字段去重 mysql根据多个字段去重_字段_03


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’, ‘左将军威武’);