经典SQL语句，可以让行的数据当列来显示

最新推荐文章于 2020-10-13 09:26:17 发布

weixin_34248849

最新推荐文章于 2020-10-13 09:26:17 发布

阅读量173

点赞数

本文介绍了一种复杂的SQL查询方法，该方法用于从产品库存数据库中选取特定条件的数据，并按产品编号降序排列。此查询涉及多个CASE WHEN语句来对不同性别和尺码的产品数量进行汇总，同时获取最大价格及总计数。

SELECT TOP 100 PERCENT MAX(DISTINCT id) AS id, product_no, color, color2, 
      product_type, SUM(number4) AS number, 
      SUM(CASE WHEN ((product_size = '34' AND product_class = '女鞋') OR
      (product_size = '38' AND product_class = '男鞋') OR
      (product_class NOT IN ('女鞋', '男鞋'))) THEN number4 ELSE 0 END) AS size34, 
      SUM(CASE WHEN ((product_size = '35' AND product_class = '女鞋') OR
      (product_size = '39' AND product_class = '男鞋')) THEN number4 ELSE 0 END) 
      AS size35, SUM(CASE WHEN ((product_size = '36' AND product_class = '女鞋') OR
      (product_size = '40' AND product_class = '男鞋')) THEN number4 ELSE 0 END) 
      AS size36, SUM(CASE WHEN ((product_size = '37' AND product_class = '女鞋') OR
      (product_size = '41' AND product_class = '男鞋')) THEN number4 ELSE 0 END) 
      AS size37, SUM(CASE WHEN ((product_size = '38' AND product_class = '女鞋') OR
      (product_size = '42' AND product_class = '男鞋')) THEN number4 ELSE 0 END) 
      AS size38, SUM(CASE WHEN ((product_size = '39' AND product_class = '女鞋') OR
      (product_size = '43' AND product_class = '男鞋')) THEN number4 ELSE 0 END) 
      AS size39, SUM(CASE WHEN ((product_size = '40' AND product_class = '女鞋') OR
      (product_size = '44' AND product_class = '男鞋')) THEN number4 ELSE 0 END) 
      AS size40, MAX(price) AS price, MAX(price2) AS price2, MAX(price3) AS price3, 
      MAX(price4) AS price4
FROM dbo.tbl_product_store
WHERE (isnum2 = 1) AND (number4 > 0)
GROUP BY product_no, color, color2, product_type
ORDER BY product_no DESC