如何将卖家销量统计与卖家信息表关联为单条SQL查询

现有两条SQL语句 #

1. 统计每个seller_id的商品销量 #

SELECT `seller_id`, COUNT(`seller_id`) AS Products_selling
FROM `Producs`
GROUP BY `seller_id` 
ORDER BY Products_selling DESC   

2. 内连接seller与producs表获取卖家信息 #

SELECT name_seller, Producs.seller_id, model, Brand
FROM seller INNER JOIN producs
ON seller.seller_id=producs.seller_id
ORDER BY seller_id ASC

你尝试的错误查询 #

SELECT name_seller COUNT(name_seller) FROM (
    SELECT name_seller, producs.seller_id, model, Brand
    FROM seller INNER JOIN producs
    ON seller.seller_id=producs.seller_id
    ORDER BY name_seller ASC
) as unit

正确的SQL写法 #

方法一：先统计销量再关联卖家表（性能更优） #

SELECT s.name_seller, p.seller_id, p.Products_selling
FROM (
    SELECT `seller_id`, COUNT(`seller_id`) AS Products_selling
    FROM `Producs`
    GROUP BY `seller_id`
) AS p
INNER JOIN seller AS s ON p.seller_id = s.seller_id
ORDER BY p.Products_selling DESC

方法二：关联表后直接统计 #

SELECT s.name_seller, s.seller_id, COUNT(p.seller_id) AS Products_selling
FROM seller AS s
INNER JOIN producs AS p ON s.seller_id = p.seller_id
GROUP BY s.seller_id, s.name_seller
ORDER BY Products_selling DESC

错误原因说明 #

你之前的查询有两个明显问题：

1. 字段语法错误：SELECT name_seller COUNT(name_seller) 里两个字段之间缺少逗号分隔；
2. 逻辑错误：子查询里的ORDER BY不影响统计逻辑，且外层没有按卖家分组，直接统计会得到所有商品的总数，而非每个卖家的单独销量。

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