问题:前期开发的需求没有对某个数据表的某个字段进行唯一性校验,现在需要把有重复的数据行合并到一起, 并且重复数据中取ID最大的一条
创建表

1
2
3
4
5
6
CREATE TABLE `user` (
`id` INT(11) NOT NULL,
`username` VARCHAR(50) NULL DEFAULT NULL,
`email` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)

表中插入的数据

1
2
3
4
5
6
7
8
+----+----------+------------------+
| id | username | email |
+----+----------+------------------+
| 1 | zonghua | zh.h@outlook.com |
| 2 | lala | zh.h@outlook.com |
| 3 | yoyo | yoyo@outlook.com |
| 4 | coco | coco@outlook.com |
+----+----------+------------------+

表中的第一和第二行的email重复,在使用emial确定唯一身份的情况下,去掉第一行或者第二行。

方法1

  1. 把用户表按照email分组(或者组合其他条件分组)取每组id最大的id;
  2. 把原来用户表左关联到id最大的虚拟表,即b;
  3. 关联后右边没有的id就是两表的差集,即重复email但id不是最大的行;
1
2
3
select a.id from `user` a left join (
select max(id) as `id`, email from `user` group by email) b on a.id = b.id
where isnull(b.id)

或者直接用in查询;

1
2
3
4
select id from `user` where id not in(
select id from (
select max(id) id, email from `user` group by email) as b
)

得到。

1
2
3
4
5
+----+----------+------------------+
| id | username | email |
+----+----------+------------------+
| 1 | zonghua | zh.h@outlook.com |
+----+----------+------------------+

把重复数据id存入临时表;

1
2
3
4
5
create table temp_user as  (
select a.id from `user` a left join (
select max(id) as `id`, email from `user` group by email) b on a.id = b.id
where isnull(b.id)
)

然后删除;

1
delete from `user` where id in (select id from `temp_user`)

结果。

1
2
3
4
5
6
7
+----+----------+------------------+
| id | username | email |
+----+----------+------------------+
| 2 | lala | zh.h@outlook.com |
| 3 | yoyo | yoyo@outlook.com |
| 4 | coco | coco@outlook.com |
+----+----------+------------------+

MySQL 语句执行顺序

  1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  2. ON: 对虚表VT1进行ON筛选,只有那些符合的行才会被记录在虚表VT2中。
  3. JOIN: 如果指定了OUTER JOIN(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3, rug from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
  4. WHERE: 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
  5. GROUP BY: 根据group by子句中的列,对VT4中的记录进行分组操作,产生VT5.
  6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作,产生表VT6.
  7. HAVING: 对虚拟表VT6应用having过滤,只有符合的记录才会被 插入到虚拟表VT7中。
  8. SELECT: 执行select操作,选择指定的列,插入到虚拟表VT8中。
  9. DISTINCT: 对VT8中的记录进行去重。产生虚拟表VT9.
  10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作,产生虚拟表VT10.
  11. LIMIT:取出指定行的记录,产生虚拟表VT11, 并将结果返回。

注意项

1. distinct的唯一是select所有字段的组合条件
2. group by 条件必须在 select 里面
3. 先group by后order by
4. exist是做真值判断,in是形成虚拟表
5. in后面只能有一个字段

分组聚合

分组统计具有同名部门的部门名称+ID、员工总数、最低薪水、最高薪水、薪水总计。

1
2
3
select concat(d.name, '-',d.id), count(u.id), min(u.salary), max(u.salary), sum(u.salary)
from `department` d left join `user` u on d.id = u.department_id
group by d.id, d.name