SQL技术咨询：统计盈利售出产品数量及查询语句优化

先说说你原语句的问题 #

• 表连接逻辑混乱：同时用逗号分隔表和INNER JOIN重复引用transactions表，数据库会因表引用歧义报错。连接表请统一用一种方式，推荐使用INNER JOIN语法。
• 分组规则不符合SQL规范：在多数严格SQL模式下（如MySQL的ONLY_FULL_GROUP_BY），SELECT中出现的非聚合字段（比如p.id）必须加入GROUP BY，否则会报错。
• 盈利计算逻辑错误：sum(t.total_price / t.quantity)是把每笔交易的单位售价求和，这不是总盈利的正确计算方式，毫无业务意义。

关于total_price与quantity的运算说明 #

你纠结的点很明确：仅靠total_price怎么算盈利？
其实total_price就是每笔交易中该产品的总销售额（比如卖5个单价10元的产品，total_price就是50）。单单位售价确实是total_price / quantity，但计算总盈利时不用拆到单位维度——直接用所有交易的total_price总和减去所有交易的quantity总和×产品进价p.price，得到的就是该产品的总盈利，这才是符合业务逻辑的计算方式。

可用的查询语句 #

如果目标是统计盈利产品的数量，可以这么写：

支持CTE的SQL版本（如MySQL 8+、PostgreSQL等） #

-- 先计算每个产品的总盈利
WITH product_profit AS (
    SELECT 
        t.product_id,
        SUM(t.total_price) - SUM(t.quantity) * p.price AS total_profit
    FROM transactions t
    INNER JOIN products p ON t.product_id = p.id
    GROUP BY t.product_id, p.price
)
-- 统计盈利大于0的产品数量
SELECT COUNT(*) AS profitable_product_count
FROM product_profit
WHERE total_profit > 0;

不支持CTE的旧版SQL（如MySQL 5.x） #

SELECT COUNT(*) AS profitable_product_count
FROM (
    SELECT 
        t.product_id,
        SUM(t.total_price) - SUM(t.quantity) * p.price AS total_profit
    FROM transactions t
    INNER JOIN products p ON t.product_id = p.id
    GROUP BY t.product_id, p.price
) AS sub
WHERE total_profit > 0;

额外提示 #

• 若想查看每个盈利产品的具体盈利情况，直接查询product_profit子集中的内容并保留WHERE total_profit > 0条件即可。
• 语句中未使用除法，无需担心quantity为0导致的报错问题。

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