group by 分组与聚合函数对查询原表其他关联字段的影响及改进方法

第一种思路：
先查询每个用户最近一天登录的日期date及用户号user_id、client_id形成一张表，再与用户(user)表、客户端(client)表进行联结，匹配对应的用户的名字，以及用户用的设备的名字，并且将查询结果按照user的name升序排序；
首先使用了错误的代码如下：

select u.name as u_n,
       c.name as c_n,
       m.max_d as date
from(select user_id,
            client_id,
            max(date) as max_d
     from login
     group by user_id) as m join user as u on m.user_id=u.id
                            join client as c on m.client_id=c.id
order by u.name;

输出结果：

研究结果发现，错误出在如下代码：

select user_id,
       client_id,
       max(date) as max_d
from login
group by user_id

错误代码输出如下：

问题定位是：group by 分组与聚合函数max()对查询原表其他关联字段client_id未能做到准确匹配的造成的
改进方法1：使用where (user_id,date) in ()

select u.name as u_n,
       c.name as c_n,
       m.max_d as date
from(select user_id,
            client_id,
            date as max_d
     from login
     where (user_id,date) in (select user_id,
                                     max(date)                             
                              from login
                              group by user_id) 
    ) as m join user as u on m.user_id=u.id
           join client as c on m.client_id=c.id
order by u.name;

改进方法2：使用窗口函数

select u.name as u_n,
       c.name as c_n,
       m.max_d as date
from(select user_id,
            client_id,
            date as max_d
     from(select user_id,
                 client_id,
                 date,
                 row_number() over(partition by user_id order by date desc) as row_n
          from login) as a
     where row_n=1) as m join user as u on m.user_id=u.id
                         join client as c on m.client_id=c.id
order by u.name;

第二种思路：
先将登录(login)记录表、用户(user)表、客户端(client)表进行联结，再用where (user_id,date) in ()匹配对应的每个用户最近一天登录的日期date及用户号user_id，并且将查询结果按照user的name升序排序；

select u.name as u_n,
       c.name as c_n,
       l.date
from login as l  join user as u on l.user_id=u.id
                 join client as c on l.client_id=c.id
where (l.user_id,l.date) in (select user_id,max(date)
                             from login
                             group by user_id)
order by u.name;

仅供参考！