1道使用group by分组函数的面试题

前言：

如果你已经了解group by的基本用法，可以继续往下阅读，如果不知道group by是什么，可以先百度或在csdn了解一下。

进入正题：

同学发来一道面试题，题目是这个样子。如图。

好了，让我们开始演示吧。

1、首先，建表

create table AAA(
id varchar2(5),
name varchar2(10));

create table BBB(
id varchar2(5),
aid varchar2(5),
count varchar2(5),
riqidate  varchar2(10)
);

2、然后，插入数据

insert into AAA values ('1','张三');
insert into AAA values ('2','李四');

insert into BBB values ('1','1','6','2019-11-01');
insert into BBB values ('2','1','8','2019-10-29');
insert into BBB values ('3','2','9','2019-11-02');
insert into BBB values ('4','2','7','2019-11-03');
insert into BBB values ('5','2','8','2019-10-20');

 

----好了，原材料已经准备好了。就等着第3步加工完揭晓吧....建议小伙伴在阅读第3步前，可以自己拿上面的数据在自己的数据库试试哟，或许聪明的你，一次就写出来了。要是绞尽脑汁，还没有头绪，请继续阅读。

3、第三步：刚看到这道题的时候，心理有点小不屑，心想，这不就是个简单的分组函数吗，呵呵...然后各种尝试，发现，自己的sql功底还得继续提高啊。

假如只是查询a.id,a.name,最大的count这三个字段，这不是很容易：

select b.aid,a.name,max(b.count) from AAA a join BBB b on a.id=b.aid group by b.aid,a.name ;

但是，还要查日期，那么直接这么写可不可以？

select b.aid,a.name,max(b.count),b.riqidatefrom AAA a join BBB b on a.id=b.aid group by b.aid,a.name,b.riqidate;

相信，自己动手尝试过的小伙伴，一定会说:No,No,No，产生了笛卡尔积。。。

是的，A表的id和name是一一对应的，但是id和count，日期，却是一对多啊，博主刚开始也少考虑了这个情况，所以查询出来的就是笛卡尔积的结果咯....如下图：

那么，如何解决呢。我们仔细分析数据结构，无论数据多么庞大，aid和name都永远只有一个最大的count,我们要用这个条件来取日期的唯一值。所以第一想法是用子查询吧，子查询必然会有bb.count = t.count1    and bb.aid = t.aid这个条件，这样就解决了一对多产生的笛卡尔积问题。

好，开始吧：

select t.*,
       (select bb.riqidate
          from BBB bb
         where bb.count = t.count1
           and bb.aid = t.aid)
  from (select b.aid, a.name, max(b.count) as count1
          from AAA a
          join BBB b
            on a.id = b.aid
         group by b.aid, a.name) t

答案已揭晓了。小伙伴们，自己演示一下吧。