Leetcode_10 天 SQL 入门_day8

还是计算函数哈~

586. Customer Placing the Largest Number of Orders

Analysis

挑出订单数最多的顾客即可。

Code

method 1

先统计出相同 customer_number 拥有的不同的 order_number 的个数：

select distinct customer_number, count(order_number) as order_number
from Orders
group by customer_number

按 order_number 的个数倒序排列：

select distinct customer_number, count(order_number) as order_number
from Orders
group by customer_number
order by order_number desc

可以发现，第一行就是要的答案了，此时再从这个表中挑出需要的信息：

select customer_number
from (
    select distinct customer_number, count(order_number) as order_number
    from Orders
    group by customer_number
    order by order_number desc
    ) as t1
limit 1

实际上可以写的更简单一点：

select distinct customer_number
from Orders
group by customer_number
order by count(order_number) desc
limit 1

method 2

在统计出 order_number 的个数后，也可以直接挑出最大值：

select customer_number
from (
    select distinct customer_number, count(order_number) as order_number
    from Orders
    group by customer_number
    ) as t1
where t1.order_number = (select max(order_number) from t1)

但遗憾的是，这样写会报错，原因是不合语法。实际上，这是一个执行顺序的问题。
在 SQL 中，WHERE 是先于 SELECT 执行，也就是说 SQL server 在执行语句的时候，它不知道 t1 是什么，就没办法执行了（有点无力吐槽 SQL 奇奇怪怪的语法了😑）。如果还想要用这个思路来解决问题，就得先将 order_number 的个数统计出来，就需要用到 WITH AS 了。

with t1 as (select customer_number, count(order_number) as order_number from Orders group by customer_number)
select customer_number
from t1
where t1.order_number = (select max(order_number) from t1)

511. Game Play Analysis I

Analysis

找出用户的最早注册日期。

Code

select distinct player_id, min(event_date) as first_login
from Activity
group by player_id

em，这个题比较简单，本以为最小日期不能直接用 MIN 函数，没想到可以用。

1890. The Latest Login in 2020

Analysis

找出在 2020 年内用户的最后一次登录信息。

Code

method 1

找出最后的一次信息，可以借助 MAX 函数，限定 2020 年内，可以借助 WHERE。

select distinct user_id, max(time_stamp) as last_stamp
from Logins
where time_stamp >= '2020-01-01 00:00:00' and time_stamp < '2021-01-01 00:00:00'
group by user_id

method 2

实际上，限定年份的时候，可以借助日期函数 YEAR。

select user_id, max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = 2020
group by user_id

1741. Find Total Time Spent by Each Employee

Analysis

找出不同日期，不同人待在办公室的总时间。

Code

先求出每个人留在办公室的时间，用 out_time 减去 in_time 即可。

select event_day, emp_id, (out_time - in_time) as diff
from Employees

然后算出总和，按照不同的人和不同的日期将结果集组合到一起。

select distinct event_day as day, emp_id, sum(diff) as total_time
from (
    select event_day, emp_id, (out_time - in_time) as diff
    from Employees
    ) as t1
group by event_day, emp_id

也可以写的更简单一点：

select distinct event_day as day, emp_id, sum(out_time - in_time) as total_time
from Employees
group by event_day, emp_id

Summary

除了第一个题的 LIMIT 没想到外，另外 3 个题比较简单。另外，第一个题的第二种思路感觉比较合理一点（就实际运用而言），但是需要用到 WITH AS，SQL 语法真是奇怪啊。

---