SQL常见技巧：子查询 + 聚合函数

主要针对 聚合函数 + 多字段的情况

题目一

Find the total number of invoices for each customer along with the customer’s full name, city and email.

像这种题目就不能直在外表中使用聚合函数，而是应该在子查询中使用聚合函数或者窗口函数。
（因为聚合字段不能与非聚合字段放在一起）

SELECT t.FirstName, t.LastName, t.City, t.Email, t.cnt
FROM (
    SELECT c.*, COUNT(*) cnt
    FROM Customers c 
    JOIN Invoices i 
    ON c.CustomerID = i.CustomerID
    GROUP BY c.CustomerID
) t

+-------------+-------------+---------------------+--------------------------+-------+
| t.FirstName | t.LastName  | t.City              | t.Email                  | t.cnt |
+-------------+-------------+---------------------+--------------------------+-------+
| Luís        | Gonçalves   | São José dos Campos | luisg@embraer.com.br     |     7 |
| Leonie      | Köhler      | Stuttgart           | leonekohler@surfeu.de    |     7 |
| François    | Tremblay    | Montréal            | ftremblay@gmail.com      |     7 |
| Bjørn       | Hansen      | Oslo                | bjorn.hansen@yahoo.no    |     7 |
| František   | Wichterlová | Prague              | frantisekw@jetbrains.com |     7 |
| Helena      | Holý        | Prague              | hholy@gmail.com          |     7 |
| Astrid      | Gruber      | Vienne              | astrid.gruber@apple.at   |     7 |
| Daan        | Peeters     | Brussels            | daan_peeters@apple.be    |     7 |
| Kara        | Nielsen     | Copenhagen          | kara.nielsen@jubii.dk    |     7 |
| Eduardo     | Martins     | São Paulo           | eduardo@woodstock.com.br |     7 |
+-------------+-------------+---------------------+--------------------------+-------+
(Output limit exceeded, 10 of 59 total rows shown)

题目二

SQL67 牛客每个人最近的登录日期(二)
这题采用的是在子查询中使用窗口函数的方式。