如何基于SQL的Trade表识别买卖同商品的盈利转售者并按利润排序

原SQL错误原因 #

你编写的SQL逻辑存在本质问题：子查询按Receiver分组后，返回的Sender字段是分组后随机匹配的单条记录的卖出方，和外层的Receiver没有对应关联，自然查不到符合要求的结果。

实现逻辑说明 #

首先明确表字段的交易含义：单条Trade记录中，Sender是卖出商品的一方（收取货款），Receiver是买入商品的一方（支付货款）。我们需要找到对同一件商品，同时有买入记录（作为Receiver）和卖出记录（作为Sender）的交易者，再计算其对应商品的总利润：总卖出收入 - 总买入成本。

正确SQL实现 #

支持CTE的数据库（MySQL 8.0+/PostgreSQL等） #

WITH user_buy AS (
    -- 统计每个用户每个商品的总买入成本
    SELECT 
        Receiver AS trader,
        Item,
        SUM(Price) AS total_buy
    FROM Trade
    GROUP BY Receiver, Item
),
user_sell AS (
    -- 统计每个用户每个商品的总卖出收入
    SELECT 
        Sender AS trader,
        Item,
        SUM(Price) AS total_sell
    FROM Trade
    GROUP BY Sender, Item
)
SELECT 
    trader,
    Item,
    (total_sell - total_buy) AS total_profit
FROM user_buy
INNER JOIN user_sell USING(trader, Item)
-- 只看盈利的转售者可以解除下一行注释
-- WHERE total_sell - total_buy > 0
ORDER BY total_profit DESC;

不支持CTE的数据库（如MySQL 5.x） #

SELECT 
    buy.trader,
    buy.Item,
    (sell.total_sell - buy.total_buy) AS total_profit
FROM (
    SELECT Receiver AS trader, Item, SUM(Price) AS total_buy
    FROM Trade GROUP BY Receiver, Item
) AS buy
INNER JOIN (
    SELECT Sender AS trader, Item, SUM(Price) AS total_sell
    FROM Trade GROUP BY Sender, Item
) AS sell ON buy.trader = sell.trader AND buy.Item = sell.Item
ORDER BY total_profit DESC;

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