mysql - 查询一张表中的项目

Question

我正在寻找一个基于表格的查询，其中我拥有商店中的所有商品。该表包含以下列:

location: Shop Name
product: Item Number
attributinstance_id: not in use so it always is NULL
Units:count of Items

我喜欢编写一个查询，我可以在其中查看其他商店中有哪些商品以及其中有多少商品，并且结果应该位于同一行。

例如:

       Shop 1   Shop 2  Shop 3
item 1    1        2      5   

我让它与所有树商店中可用或可用的元素一起运行，但是当我查找仅在其中一个树商店中可用的元素时，它们不会显示。有人有想法吗？

到目前为止我的想法:

SELECT Distinct  
products.name AS Artikel,
eins.units AS Europa_Center,
zwei.Units AS O_Strasse,
drei.units AS K_Strasse

FROM stockcurrent eins
LEFT JOIN stockcurrent zwei
ON (eins.product = zwei.product
  AND eins.location ='0'
  AND zwei.location ='a59cb899-27f4-460c-b5df-89a89eaaef75')
LEFT JOIN stockcurrent drei
ON (zwei.product = drei.product 
    AND zwei.location ='a59cb899-27f4-460c-b5df-89a89eaaef75' 
    AND drei.location ='3b53adf5-eaee-4a13-b22b-39d50b14e497')
JOIN products
ON products.id = eins.product
JOIN locations
ON locations.id = eins.location

整个想法适用于 POS 系统 Unicentas 4.3。该表如下所示:

Location  Product atributsinstance_id Units shop_1    item_1       NULL             5shop_2    item_1       NULL             1shop_3    item_1       NULL             0shop_2    item_2       Null             3  shop_1    item_3       Null             2shop_2    item_3       Null             4shop_3    item_3       Null             1

Answer 1

您需要条件聚合:

 SELECT product_id,
        SUM(CASE WHEN location = 'SHOP1' THEN units ELSE 0 END) as shop_1,
        SUM(CASE WHEN location = 'SHOP2' THEN units ELSE 0 END) as shop_2,
        SUM(CASE WHEN location = 'SHOP3' THEN units ELSE 0 END) as shop_3
 FROM stock
 GROUP BY product_id