MySQL 自连接表

Question

我有一个名为 Inventory 的表，其结构如下:

Location_ID |Item_ID |Stock
1           |A       |100
1           |B       |500
1           |C       |300
2           |A       |10
2           |B       |20

字段 location_ID 和 item_ID 是组合键。我想从该单个表中生成以下数据:

Item_ID |Stock_1 |Stock_2
A       |100     |10
B       |500     |20
C       |300     |0

我尝试编写几个自连接查询，但它不起作用。还有一个问题: Item_ID C does not exist on location_ID 2. 如果结果表不存在，我们如何将值'0'放在结果表中？头脑更聪明的人能阐明什么吗？

Answer 1

select DIS_ITME_ID,
       IFNULL ((select stock
                 from inventory
                where location_id = 1
                  and item_id = DIS_ITEM_ID), 0) as stock_1,
       IFNULL ((select stock
                 from inventory
                where location_id = 2
                  and item_id = DIS_ITEM_ID), 0) as stock_2
  from (select distinct item_ID as DIS_ITEM_ID from inventory)