如何用纯SQL的WHERE IN语句获取数组内每个用户的最近100条交易

已知数据表结构如下：

create table user (    
  id int     
);    
    
create table transaction (    
  user_id int,    
  date timestamp,    
  amount int    
);    

你需要从给定的100个用户ID数组中，获取每个用户的最近100条交易记录，无需循环查询，用纯SQL即可实现，核心思路是利用窗口函数按用户分区并排序筛选，以下是不同数据库的实现方案：

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

直接用ROW_NUMBER()窗口函数按用户ID分区，交易时间倒序编号，筛选前100条：

SELECT t.user_id, t.date, t.amount
FROM (
    SELECT 
        user_id, 
        date, 
        amount,
        -- 按用户分区，交易时间倒序生成序号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
    FROM transaction
    -- 替换括号内为你的100个用户ID数组
    WHERE user_id IN (1, 2, 3, ..., 100)
) AS t
-- 取每个用户的前100条交易
WHERE t.rn <= 100;

如果用户ID数组来自其他查询结果，也可以用CTE先封装：

WITH target_users AS (
    -- 这里替换为你的用户ID查询逻辑，比如从其他表获取
    SELECT id FROM user WHERE [你的筛选条件]
)
SELECT t.user_id, t.date, t.amount
FROM (
    SELECT 
        user_id, 
        date, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
    FROM transaction
    WHERE user_id IN (SELECT id FROM target_users)
) AS t
WHERE t.rn <= 100;

不支持窗口函数的老版本数据库（如MySQL 5.x） #

可以用关联子查询统计每个交易的后续记录数，筛选前100条：

SELECT t1.user_id, t1.date, t1.amount
FROM transaction t1
WHERE t1.user_id IN (1, 2, 3, ..., 100)
AND (
    -- 统计同用户下交易时间晚于等于当前记录的数量
    SELECT COUNT(*) 
    FROM transaction t2
    WHERE t2.user_id = t1.user_id 
    AND t2.date >= t1.date
) <= 100;

注意：这种方法性能低于窗口函数，数据量大时建议优先升级数据库版本或用窗口函数方案。

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