SELECT 语句中的子查询 (MySQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9298936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subquery in SELECT statement (MySQL)
提问by Cpt. Awesome
I'm creating an SQL statement that will return a list of products and the quantity of each one of them I can find in every store.
我正在创建一个 SQL 语句，该语句将返回一个产品列表以及我可以在每家商店中找到的每个产品的数量。
The structure of my table (with some sample data) is as follows:
我的表的结构（带有一些示例数据）如下：
productID - size - color - stock - storeID 1 - S - RED01 - 1 - BCN 1 - S - RED01 - 3 - MAD 2 - S - YEL02 - 0 - BCN 2 - S - YEL02 - 2 - MAD 1 - S - RED01 - 1 - BCN2
I need a result similar to this one:
我需要一个类似于这个的结果：
productID - size - stockBCN (BCN + BCN2) - stockMAD 1 - S - 2 - 3 2 - S - 0 - 2
I'm using this statement, and works fine just for an unique product
我正在使用此语句，并且仅适用于独特的产品
SELECT DISTINCT prodID, size,
(SELECT SUM(stock) FROM stocks WHERE storeID IN ('BCN','BCN2') AND prodID = 1 AND size = 'S' AND color = 'RED01' GROUP BY prodID, size, color) AS stockBCN,
(SELECT SUM(stock) FROM stocks WHERE storeID = 'MAD' AND prodID = 1 AND size = 'S' AND color = 'RED01' GROUP BY prodID, size, color) AS stockMAD,
FROM stocks WHERE storeID IN ('BCN','BCN2','MAD')
AND prodID = (1) AND size = 'S' AND color = 'RED01'
How can I change it to make it work for all the rows in the table?
如何更改它以使其适用于表中的所有行？
回答by Kevin Stricker
You probably want to leverage the GROUP BYclause instead of going with DISTINCTthere, i.e.:
您可能想利用该GROUP BY条款而不是去DISTINCT那里，即：
SELECT productId, Size,
SUM(CASE WHEN storeID IN ('BCN', 'BCN2') THEN stock ELSE 0 END) AS stockBCN,
SUM(CASE WHEN storeID = 'MAD' THEN stock ELSE 0 END) AS stockMAD
FROM stocks
GROUP BY productId, Size
回答by xQbert
IMO prime time to use a case statement
IMO 使用 case 语句的黄金时间
Select productID, Size, Color,
SUM(CASE WHEN STOREID IN ('BCN','BCN2') THEN stock else 0 end) as StockBCN,
SUM(Case WHEN STOREID = 'MAD') THEN stock else 0 end) as StockMad
FROM STOCKS
Where storeID in ('BCN', 'BCN2','MAD')
GROUP BY productID, Size, Color
