如何编写SQL查询找出交易参与次数最多的用户及其交易次数

嘿，这个需求的核心就是把用户作为卖家和买家的所有交易次数合并统计，然后找出次数最多的用户。我给你整理了两种实用的SQL实现方式，还有详细的步骤说明：

第一步：先把所有参与交易的用户ID统一列出来 #

首先，我们需要把原表中seller_id和buyer_id拆成单独的用户ID行——毕竟不管是买还是卖，都算用户参与了一次交易。这里用UNION ALL来做最适合，因为它不会去重，刚好能保留每一次交易的记录：

-- 拆分卖家和买家ID为统一的user_id列
SELECT seller_id AS user_id FROM transactions
UNION ALL
SELECT buyer_id AS user_id FROM transactions

举个例子，原表中第一行的交易(1,4)会被拆成两行：1和4，这样每一行就代表用户参与了一次交易。

第二步：统计每个用户的总交易次数 #

基于上面的结果，我们用GROUP BY和COUNT()来计算每个用户的总次数：

SELECT user_id AS ID, COUNT(*) AS n_trans
FROM (
    SELECT seller_id AS user_id FROM transactions
    UNION ALL
    SELECT buyer_id AS user_id FROM transactions
) AS all_users
GROUP BY user_id

这一步运行后，就能得到每个用户的交易次数，比如用户2是3次，用户4也是3次。

第三步：筛选出交易次数最多的用户 #

接下来要找出所有达到最高交易次数的用户，这里有两种写法：

写法一：用子查询获取最大次数（兼容所有数据库） #

这种写法不需要依赖窗口函数，适合所有支持基本SQL语法的数据库：

SELECT ID, n_trans
FROM (
    -- 先统计每个用户的总交易次数
    SELECT user_id AS ID, COUNT(*) AS n_trans
    FROM (
        SELECT seller_id AS user_id FROM transactions
        UNION ALL
        SELECT buyer_id AS user_id FROM transactions
    ) AS all_users
    GROUP BY user_id
) AS user_trans_counts
-- 筛选出次数等于最大次数的用户
WHERE n_trans = (
    SELECT MAX(n_trans)
    FROM (
        SELECT COUNT(*) AS n_trans
        FROM (
            SELECT seller_id AS user_id FROM transactions
            UNION ALL
            SELECT buyer_id AS user_id FROM transactions
        ) AS all_users
        GROUP BY user_id
    ) AS max_counts
)

写法二：用窗口函数（更简洁，适合MySQL 8.0+、PostgreSQL等） #

如果你的数据库支持窗口函数（比如MySQL 8.0及以上版本），用RANK()函数会更简洁，不需要重复写统计逻辑：

SELECT ID, n_trans
FROM (
    SELECT 
        user_id AS ID, 
        COUNT(*) AS n_trans,
        -- 按交易次数降序排名，次数相同的用户排名一致
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM (
        SELECT seller_id AS user_id FROM transactions
        UNION ALL
        SELECT buyer_id AS user_id FROM transactions
    ) AS all_users
    GROUP BY user_id
) AS ranked_users
-- 筛选排名第一的用户（也就是次数最多的）
WHERE rnk = 1

最终结果 #

不管用哪种写法，执行后都会得到你想要的结果：

ID | n_trans
---|--------
2  | 3
4  | 3

小提示 #

• 为什么用UNION ALL而不是UNION？因为UNION会自动去重，但我们需要把每一次交易都算进去（比如用户2作为买家的两次交易都要统计），所以UNION ALL才是正确的选择。
• 题目里说同一交易中用户不能同时当买家和卖家，也不会有重复的买家-卖家组合，所以我们的统计逻辑不会有重复计算的问题，可以放心使用。

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