SELECT 语句中的子查询 (MySQL)

我正在创建一个 SQL 语句，它将返回一个产品列表以及我可以在每个商店中找到的每种产品的数量.

我的表结构(带有一些示例数据)如下:

<上一页>productID - 尺寸 - 颜色 - stock - storeID1 - S - RED01 - 1 - BCN1 - S - RED01 - 3 - 疯狂2 - S - YEL02 - 0 - BCN2 - S - YEL02 - 2 - 疯狂1 - S - RED01 - 1 - BCN2

我需要一个类似的结果:

<上一页>productID - 尺寸 - stockBCN (BCN + BCN2) - stockMAD1 - S - 2 - 32 - S - 0 - 2

我正在使用此声明，并且仅适用于独特的产品

SELECT DISTINCT prodID, size,(SELECT SUM(stock) FROM stock WHERE storeID IN ('BCN','BCN2') AND prodID = 1 AND size = 'S' AND color = 'RED01' GROUP BY prodID, size, color) AS stockBCN,(从股票中选择 SUM(库存)，其中 storeID = 'MAD' AND prodID = 1 AND size = 'S' AND color = 'RED01' GROUP BY prodID, size, color)作为 stockMAD，FROM stock WHERE storeID IN ('BCN','BCN2','MAD')AND prodID = (1) AND size = 'S' AND color = 'RED01'

如何更改它以使其适用于表中的所有行?

解决方案

你可能想利用 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从股票按产品 ID、尺寸分组

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.

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?

解决方案

You probably want to leverage the GROUP BY clause instead of going with DISTINCT there, i.e.:

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