下面来对GROUP BY后面有多个字段进行测试。
一、环境搭建
1 2 3 4 5 6 |
CREATE TABLE `work` ( `id` int DEFAULT 0, `name` varchar(20) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `work` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
插入参数
1 2 3 4 5 6 7 8 9 |
insert into work values(1,'小王','北京','java开发'); insert into work values(2,'小王','北京','.net开发'); insert into work values(3,'小王','上海','java开发'); insert into work values(4,'小王','上海','c++开发'); insert into work values(5,'小李','北京','c++开发'); insert into work values(6,'小李','上海','java开发'); insert into work values(7,'小李','天津','c开发'); insert into work values(8,'小李','天津','c++开发'); insert into work values(9,'小红','天津','c开发'); |
二、使用场景
1、查询每个人换了多少次工作。
1 2 3 4 5 6 7 8 9 |
mysql> select name,count(*) from work group by name order by name; +--------+----------+ | name | count(*) | +--------+----------+ | 小李 | 4 | | 小王 | 4 | | 小红 | 1 | +--------+----------+ 3 rows in set (0.00 sec) |
2、查询每个人在同一地点换了多少次工作。
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select name,address,count(*) from work group by name,address order by name; +--------+---------+----------+ | name | address | count(*) | +--------+---------+----------+ | 小李 | 上海 | 1 | | 小李 | 北京 | 1 | | 小李 | 天津 | 2 | | 小王 | 上海 | 2 | | 小王 | 北京 | 2 | | 小红 | 天津 | 1 | +--------+---------+----------+ 6 rows in set (0.00 sec) |
3、查询每个人在同一地点换了多少次工作,然后显示出大于1次的地点。
1 2 3 4 5 6 7 8 9 |
mysql> select name,address,count(*) from work group by name,address having count(1) > 1; +--------+---------+----------+ | name | address | count(*) | +--------+---------+----------+ | 小李 | 天津 | 2 | | 小王 | 上海 | 2 | | 小王 | 北京 | 2 | +--------+---------+----------+ 3 rows in set (0.00 sec) |
4、查询每个人在同一地点换了多少次工作,然后显示出大于1次的地点,并查找出对应最新的插入记录(id较大的)。
1 2 3 4 5 6 7 8 9 |
mysql> select max(id),name,address,count(1) from work group by name,address having count(1) > 1; +---------+--------+---------+----------+ | max(id) | name | address | count(1) | +---------+--------+---------+----------+ | 8 | 小李 | 天津 | 2 | | 4 | 小王 | 上海 | 2 | | 2 | 小王 | 北京 | 2 | +---------+--------+---------+----------+ 3 rows in set (0.00 sec) |
5、删除最新插入记录(查询每个人在同一地点换了多少次工作,然后显示出大于1次的地点,并查找出对应最新的插入记录(id较大的))。
1 2 |
mysql> delete from work where id in (select max(id) from work group by name,address having count(1) > 1); ERROR 1093 (HY000): You can't specify target table 'work' for update in FROM clause |
刚开始按照自己的逻辑就这样写了一条删除语句,执行的时候报错了,有点懵逼了。后来查了文章,说不能先select出同一表中的某些值,再delete/update这个表(在同一语句中)。不知道MySQL为什么不允许这样操作,猜,可能是担心更新的表与查询的表为同一表会存在嵌套递归?还是担心效率的问题呢?这个问题只出现于MySQL,Ms SQL和Oracle不会出现此问题。
其实嵌套一下就OK了,后来改写成下面这样就行了:
1 2 3 |
delete from work where id in ( select a.id from ( select max(id) as id from work group by name,address having count(1) > 1) a); |
三、嵌套错误写法导致全表操作
但是上面这个写法是标准写法,如果说你在写SQL时写成下面这种,那么就会导致全表更新或全表删除了,一定要注意哦。
1 2 3 |
delete from work where id in ( select id from ( select max(id) from work group by name,address having count(1) > 1) a); |
按道理说这个语句应该是不被允许执行的,MySQL应该报错,反而把全表给操作了,演示如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from work; +------+--------+---------+------------+ | id | name | address | work | +------+--------+---------+------------+ | 1 | 小王 | 北京 | java开发 | | 2 | 小王 | 北京 | .net开发 | | 3 | 小王 | 上海 | java开发 | | 4 | 小王 | 上海 | c++开发 | | 5 | 小李 | 北京 | c++开发 | | 6 | 小李 | 上海 | java开发 | | 7 | 小李 | 天津 | c开发 | | 8 | 小李 | 天津 | c++开发 | | 9 | 小红 | 天津 | c开发 | +------+--------+---------+------------+ 9 rows in set (0.00 sec) |
然后执行,查看结果。
1 2 3 4 5 |
mysql> delete from work where id in (select id from (select max(id) from work group by name,address having count(1) > 1) a); Query OK, 9 rows affected (0.01 sec) mysql> select * from work; Empty set (0.00 sec) |
悲剧就此产生,等着恢复数据吧。
当然你也可以把下面这种写法:
1 2 3 |
delete from work where id in ( select a.id from ( select max(id) as id from work group by name,address having count(1) > 1) a); |
改成
1 2 3 |
delete from work where id in ( select * from ( select max(id) from work group by name,address having count(1) > 1) a); |
都是正确的。
完结。。。