可以以数据内容当列名来统计数据

最新推荐文章于 2024-06-30 18:41:59 发布

转载最新推荐文章于 2024-06-30 18:41:59 发布 · 92 阅读

本文介绍了一条复杂的SQL语句，该语句用于统计特定条件下不同产品型号、颜色及类型的库存数量，并按产品编号降序排列。同时，针对不同的鞋类产品尺寸进行分类汇总，包括价格信息的最大值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

此SQL语句是来统计库存数量的

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