此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
![2010102023085374.jpg](https://i-blog.csdnimg.cn/blog_migrate/ca0f5d9bc7cbc16051a9527bb258b8bf.jpeg)
![2010102023115412.jpg](https://i-blog.csdnimg.cn/blog_migrate/4b56667353b8597ae0f1860cd1561397.jpeg)