SQL Antipattern 之 分组查询

我们通常为分组查询提出其他特别的要求，例如我想获得每个用户最后一次评论的时间，以及每个用户最后一次的评论的ID。看上去好像这个要求和合理，但是我们却无法只单单用聚合函数去实现。
以下这个例子是获得各个管理员最后一次提交代码的时间：

 select a.username,max(c.commit_date) from commit_recode c 
 join admins a  using(admin_id) group by a.admin_id; 

回到刚刚那个问题，我们如何去获得这个最后一次提交的代码记录的ID呢？通常我们会用如下的方式去做：

 select a.username,max(c.commit_date),max(c.recode_id) 
 from commit_recode c join admins a  using(admin_id) group by a.admin_id; 

貌似很合理，但是你能确保最后一次提交代码的记录就是ID最大的记录吗？貌似也不太能保证，每一种情况都是ID最大就是最后一条提交的记录，毕竟是否为最后一条提交我们还是要看记录时间的字段，所以这种方式也略显鸡肋。

其实实现这种需求不一定要用集合函数，其实有非常多的方式方法去实现。

首先，我先贴出一下需要使用到的两张表的表结构：

insert into admins(username,userpwd,image_path) 
values
('TONY','PWD','abc.jpg'),
('CHAO','PWD',NULL),
('ADMIN','PWD','admin.jpg'),
('YAN','PWD','YAN.jpg');

create table commit_recode(recode_id int primary key auto_increment,recode_text varchar(100),commit_date date,admin_id int,
foreign key (admin_id) references admins(admin_id));

insert into commit_recode(recode_text,commit_date,admin_id)
values
('ADMIN: FIRST COMMIT CODE!','2017-1-8',3),
('TONY: FIRST COMMIT CODE!','2017-1-5',1),
('ADMIN: LAST COMMIT CODE!','2017-8-8',3),
('TONY: LAST COMMIT CODE!','2017-3-5',1),
('ADMIN: ONE MORE COMMIT CODE!','2017-8-8',3),
('TONY: ONE MORE COMMIT CODE!','2017-3-5',1);

第一种方式：使用子查询

select a1.username,c1.commit_date,c1.recode_id from commit_recode c1 
join admins a1 using(admin_id) 
where not exists (
select * from commit_recode c2 join admins a2 using(admin_id)
 where a1.admin_id = a2.admin_id and c1.commit_date < c2.commit_date
);

查询结果如下：

可以看到我查出来4条数据，因为每个管理员最后一天都提交了两次代码。之后我们会尝试获得最后一天最后ID最大的记录。

第二种方式：衍生表

select a1.username,c1.commit_date,max(c1.recode_id) from commit_recode c1 
join admins a1 on c1.admin_id = a1.admin_id 
left join (
commit_recode c2 join admins a2 on c2.admin_id = a2.admin_id ) on 
( (c1.admin_id = c2.admin_id and  c2.commit_date > c1.commit_date) )
where c2.recode_id is null group by a1.username,c1.commit_date;

不着急，慢慢看总会看懂的。但是这里已经做获得最大ID作为最后一次提交的记录，但是使用max(c1.recode_id)并不是唯一的选择，看看以下这个方式：

select a1.username,c1.commit_date,c1.recode_id from 
commit_recode c1 join admins a1 on c1.admin_id = a1.admin_id 
left join (commit_recode c2 join admins a2 on c2.admin_id = a2.admin_id ) on 
(
    (c1.admin_id = c2.admin_id and  c2.commit_date > c1.commit_date) 
or
    (c1.admin_id = c2.admin_id and 
    c2.commit_date = c1.commit_date and 
    c1.recode_id  < c2.recode_id)
 )
  where c2.recode_id is null;

最后一种方法：外联查询

 select m.username,m.last_commit_date,max(c1.recode_id) as recode_id 
 from commit_recode c1 join admins a1 using(admin_id) JOIN (
select a2.username username,max(c2.commit_date) as last_commit_date 
from commit_recode c2 join admins a2 using(admin_id) group by a2.admin_id
 ) as m  
 on m.last_commit_date = c1.commit_date group by m.username,m.last_commit_date;

其实使用SQL查询有非常多的方法，这个是我在复习SQL的时候写的一些干货。