SQL67 牛客每个人最近的登录日期(二)

这题很经典，归到错题本。

1. 题目

• 题目来源：SQL67 牛客每个人最近的登录日期(二)

• 题目描述
  查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

• 三张表
  
  
  

• 所需结果
  

2. 题解

思路：
所求三个字段皆为题中表格的字段，这题主要考察【最近一天等登陆】的表示，以及表格连接。
考察的知识点是聚合函数、GROUP BY 以JOIN。

（过程中发现，这题使用LEFT JOIN 和 INNER JOIN都正确，因为两张表互相匹配）

最近一天登陆也就是日期最大的时候，可以使用GROUP BY 再对用户进行分组求解。
但问题的关键是使用了GROUP BY对SELECT语句是有限制的：字段自能是分组字段或者聚合字段，不能有多余字段。

以下是最常见的错误示范：

# wrong answer
SELECT u.name, 
       c.name, 
       MAX(l.date)
FROM login l
    JOIN user u
    ON l.user_id = u.id
    JOIN client c
    ON l.client_id = c.id
GROUP BY u.name
ORDER BY u.name

错误的原因是，无法找到对应的 客户端名（c.name）。
解决的办法是，这个最大日期是可以确定的，用这个字段去匹配和筛选就可以了。

大致有两种方法可以实现：子查询和窗口函数。

2.1 子查询

子查询是在内表对【最大日期】这个条件进行限制，有两种思路使得date和user_id同时满足：

① 双重循环

② 直接使用元组

关联子查询

# 关联子查询1
SELECT u.name, c.name, l.date
FROM login l
    JOIN user u
    ON l.user_id = u.id
    JOIN client c
    ON l.client_id = c.id
WHERE l.date = (SELECT MAX(date)
                FROM login l2
                    JOIN user u2
                    ON l2.user_id = u2.id
                WHERE u2.name = u.name  # use value from outer loop
                GROUP BY u2.name)
ORDER BY u.name

元组查询

# 元组查询（跟关联子查询本质一样）

SELECT u.name AS u_n ,c.name AS c_n ,l.date
FROM login l
JOIN user u 
    ON l.user_id = u.id
JOIN client 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_n;

2.2 窗口函数

将表三张表格进行连接之后，直接使用排序窗口函数 找到【最大日期】 进行行筛选。

## 窗口函数

SELECT u.name u_n, 
       c.name c_n, 
       t.date 
FROM (SELECT l.*, 
             ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date DESC) rk
      FROM login l
     ) t 
LEFT JOIN user u
     ON t.user_id = u.id
LEFT JOIN client c
     ON t.client_id = c.id
WHERE rk = 1
# GROUP BY t.user_id   # 注意：不能使用分组了
ORDER BY u.name

3. 反思

以后碰到这种 聚合 + 多个字段 的情况，就不能考虑直接使用GROUP BY了！
新技能：元组查询