SQL查询如何实现foreach效果获取每个关联用户的前100条数据

你原有写法的TOP 100是对整个查询结果集生效，因此会直接返回所有匹配前10个用户的条目中的前100条，无法实现每个用户各取100条的需求。

核心思路是使用窗口函数ROW_NUMBER()对每个用户关联的条目分组排序打标，再筛选每个组内序号小于等于100的条目即可，通用写法如下（适配SQL Server、MySQL 8.0+、PostgreSQL、Oracle等支持窗口函数的数据库）：

WITH Top10Users AS (
    -- 取前10个目标用户，必须指定排序规则，否则返回结果不确定
    SELECT TOP 10 SearchedUserID
    FROM SearchedUsers
    ORDER BY [你需要的排序字段] -- 例如按用户注册时间、用户ID升序/降序，按需替换
),
UserItemsRanked AS (
    SELECT
        items.*,
        -- 按用户ID分组，组内按指定规则排序后生成行号
        ROW_NUMBER() OVER (
            PARTITION BY items.SearchedUserID 
            ORDER BY items.[你需要的条目排序字段] -- 例如按条目创建时间倒序取最新100条，按需替换
        ) AS row_num
    FROM SearchedUserItems items
    INNER JOIN Top10Users users ON items.SearchedUserID = users.SearchedUserID
)
-- 筛选每个用户的前100条
SELECT * -- 如果不需要返回row_num字段，可以手动列出需要的字段替换*
FROM UserItemsRanked
WHERE row_num <= 100

如果你使用的是不支持CTE和窗口函数的低版本数据库（例如MySQL 5.x），可以用关联子查询实现：

SELECT items.*
FROM SearchedUserItems items
INNER JOIN (
    SELECT TOP 10 SearchedUserID
    FROM SearchedUsers
    ORDER BY [你需要的排序字段]
) top_users ON items.SearchedUserID = top_users.SearchedUserID
WHERE (
    SELECT COUNT(*)
    FROM SearchedUserItems inner_items
    WHERE inner_items.SearchedUserID = items.SearchedUserID
    -- 这里的排序规则要和外层一致，保证序号计算正确
    AND inner_items.[你需要的条目排序字段] <= items.[你需要的条目排序字段]
) <= 100
ORDER BY items.SearchedUserID, items.[你需要的条目排序字段]

• 所有涉及TOP、排序的逻辑必须显式指定排序字段，否则数据库返回的结果是随机的，无法符合业务预期。
• 如果允许同一个用户的100条条目出现并列排名，可以把ROW_NUMBER()替换为RANK()或者DENSE_RANK()。

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