group_by 

 1 #PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
 2 #REPLACE INTO如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。
 3 #查询重复
 4 SELECT user_name,COUNT(*) AS `count` FROM user_table GROUP BY user_name HAVING COUNT>1;
 5 SELECT * FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(peopleId) > 1) 
 6 
 7 #统计重复数据
 8 SELECT COUNT(*) AS repetitions, last_name, first_name FROM person_tbl GROUP BY last_name, first_name HAVING repetitions > 1;
 9 
10 /*请执行以下操作：
11 确定哪一列包含的值可能会重复。
12 在列选择列表使用COUNT(*)列出的那些列。
13 在GROUP BY子句中列出的列。
14 HAVING子句设置重复数大于1。*/
15 #过滤重复数据 distinct
16 SELECT  DISTINCT list1 ,list2   FROM tableName ORDER BY last_name;
17 SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);
18 
19 #删除重复数据
20 CREATE TABLE tmp AS SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name);
21 DROP TABLE person_tbl;
22 ALTER TABLE tmp RENAME TO person_tbl;
23 
24 ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
View Code

posted on 2019-12-27 15:59  HelloOcean  阅读(136)  评论(0)    收藏  举报

导航