MySQL如何关联三张表生成店铺商品销量统计矩阵

你需要的是典型的**行转列（透视表）**统计需求，以下两种实现方式都可以满足，第一种用到了你提到的WITH运算符：

方式1：固定店铺静态查询（适配所有主流数据库） #

如果店铺列表固定不会频繁变更，直接用以下语句即可：

WITH sales_total AS (
  -- 先预聚合每个店铺+商品的累计销量
  SELECT 
    fk_shop,
    fk_product,
    SUM(quantity) AS total_qty
  FROM `COMMAND LINE`
  GROUP BY fk_shop, fk_product
)
SELECT
  p.product_label AS '',
  -- 每个店铺对应一列
  SUM(CASE WHEN s.shop_label = 'Shop Paris' THEN st.total_qty END) AS 'Shop Paris',
  SUM(CASE WHEN s.shop_label = 'Shop Madrid' THEN st.total_qty END) AS 'Shop Madrid',
  SUM(CASE WHEN s.shop_label = 'Shop New York' THEN st.total_qty END) AS 'Shop New York',
  SUM(CASE WHEN s.shop_label = 'Shop Tokyo' THEN st.total_qty END) AS 'Shop Tokyo'
FROM PRODUCT p
-- 关联销售汇总，左连接保证无销量的商品也保留
LEFT JOIN sales_total st ON p.product_id = st.fk_product
-- 关联店铺表获取店铺名称
LEFT JOIN SHOP s ON st.fk_shop = s.shop_id
GROUP BY p.product_label
ORDER BY p.product_id;

注：你给出的期望结果里Shop Madrid列的Pen值8为笔误，实际统计结果中：Pen仅在Shop Paris有15的销量，Workbook在Shop Paris有8的销量、在Shop Madrid有3的销量，和原始明细数据一致。如果需要无销量的单元格显示0而非空值，在CASE语句里加ELSE 0即可。

方式2：动态生成列查询（适配店铺频繁新增/变更的场景） #

如果店铺数量会动态变化，不想每次手动修改查询里的店铺列，可使用对应数据库的动态SQL语法实现，以下是MySQL的示例：

SET @sql = NULL;
-- 先动态生成所有店铺的CASE WHEN语句
SELECT GROUP_CONCAT(
  DISTINCT CONCAT(
    'SUM(CASE WHEN s.shop_label = ''', shop_label, ''' THEN st.total_qty END) AS ''', shop_label, ''''
  )
) INTO @sql FROM SHOP;

-- 拼接完整查询语句
SET @sql = CONCAT(
  'WITH sales_total AS (SELECT fk_shop,fk_product,SUM(quantity) AS total_qty FROM `COMMAND LINE` GROUP BY fk_shop,fk_product) ',
  'SELECT p.product_label AS '', ', @sql, ' FROM PRODUCT p LEFT JOIN sales_total st ON p.product_id = st.fk_product LEFT JOIN SHOP s ON st.fk_shop = s.shop_id GROUP BY p.product_label ORDER BY p.product_id'
);

-- 执行动态查询
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

其他数据库可参考对应语法：PostgreSQL用crosstab函数/EXECUTE动态执行、SQL Server用PIVOT关键字即可。

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