内连接Client与Purchases表时，如何用LIMIT实现每个客户显示2条购买记录？

问题背景 #

原本的SQL代码可以查询所有用户的订购产品并按客户名排序：

cursor.execute('''SELECT Client.client_name, Purchases.product 
                  FROM Client
                  INNER JOIN Purchases 
                  ON Client.client_name = Purchases.client_name
                  ORDER BY client_name;''')

但直接在末尾加LIMIT 2只会返回前2行结果（仅同一个客户的2条记录），无法实现为James、George、Sophie这三个客户各显示2条购买记录的需求。

使用的两张表结构如下：

CLIENT表 #

client_name |  born  |
-----------+---------+
James      |   1988  |
George     |   1988  |
Sophie     |   1988  |

PURCHASES表 #

client_name | product   |
------------+-----------+
James      | Hard Disk |
James      | Mouse     |
George     | Book      |
George     | Keyboard  |  
Sophie     | Mouse     |
Sophie     | Coffee    |
Harry      | Cd        |
Harry      | Book      |
Lily       | Mouse     |
Lily       | Desk      |

解决方案 #

普通的LIMIT是全局限制总行数，要实现每个客户取2条记录，可以用窗口函数ROW_NUMBER()，这是适合新手的简单写法（兼容大部分现代数据库）：

最终SQL代码 #

cursor.execute('''
SELECT client_name, product
FROM (
    SELECT Client.client_name, Purchases.product,
           -- 按客户分组，给每个客户的购买记录编号
           ROW_NUMBER() OVER (PARTITION BY Client.client_name ORDER BY Purchases.product) AS row_num
    FROM Client
    INNER JOIN Purchases 
    ON Client.client_name = Purchases.client_name
    -- 筛选指定的三个客户
    WHERE Client.client_name IN ('James', 'George', 'Sophie')
) AS temp
-- 只保留每个客户的前2条记录
WHERE row_num <= 2
ORDER BY client_name;
''')

代码说明 #

• 筛选指定客户：用WHERE Client.client_name IN ('James', 'George', 'Sophie')只保留目标客户的数据
• 分组编号：ROW_NUMBER() OVER (PARTITION BY Client.client_name ORDER BY Purchases.product)会给每个客户的购买记录从1开始编号，ORDER BY Purchases.product是指定每个客户内记录的排序规则（可以换成你需要的其他字段，比如购买时间）
• 保留前2条：外层查询通过WHERE row_num <= 2过滤掉每个客户编号大于2的记录
• 最终排序：最后按客户名排序，得到你需要的输出格式

内容的提问来源于stack exchange，提问作者user20801151