查找未包含项的sql查询
查找未包含项的sql查询
提问于 2021-02-16 23:00:16
最近我有一个试题，我没能回答。现在我只想了解如何实现它。
表：
goods warehouse product brand
-------- ----------- --------- ----------
id (PK) id (PK) id (PK) id (PK)
warehouseId (FK -> warehouse.id) name name name
productId (FK -> product.id) brandId (FK -> brand.id)
quantity目的是通过数量递减的仓库检索brand.name，而品牌在仓库中没有出现的情况也应该是。
重新分配目标的第一部分相对容易：
SELECT brand.name as brand_name, sum(goods.quantity) as qty, warehouse.name as warehouse_name from brand
JOIN product ON (brand.id = product.brandid)
JOIN goods ON (product.id = goods.productid)
JOIN warehouse ON (goods.warehouseid = warehouse.id)
GROUP BY brand_name, warehouse_name
ORDER BY warehouse_name, qty desc
;结果是：
brand_name | qty | warehouse_name
------------+-----+------------------
samsung | 343 | First warehouse
sony | 200 | First warehouse
toshiba | 111 | First warehouse
siemens | 92 | First warehouse
tesla | 42 | First warehouse
samsung | 382 | Second warehouse
sony | 111 | Second warehouse
toshiba | 39 | Second warehouse
toshiba | 421 | Third warehouse
samsung | 172 | Third warehouse
sony | 23 | Third warehouse
tesla | 17 | Third warehouse问题是如何将没有由仓库展示的品牌包括在内？
这是一个恰当的结果示例：
brand_name | qty | warehouse_name
------------+-----+------------------
samsung | 343 | First warehouse
sony | 200 | First warehouse
toshiba | 111 | First warehouse
siemens | 92 | First warehouse
tesla | 42 | First warehouse
samsung | 382 | Second warehouse
sony | 111 | Second warehouse
toshiba | 39 | Second warehouse
siemens | 0 | Second warehouse
tesla | 0 | Second warehouse
toshiba | 421 | Third warehouse
samsung | 172 | Third warehouse
sony | 23 | Third warehouse
tesla | 17 | Third warehouse
siemens | 0 | Third warehouse回答 1
Stack Overflow用户
回答已采纳
发布于 2021-02-17 13:23:28
你想要的是交叉连接和左连接的组合。交叉连接是指在一个表和下一个表之间没有显式连接，从而导致第一个表中的每一行都与第二个表中的每个行连接。因此，将每种产品加入到每个仓库都会给出所有可能组合的主列表。然后，你可以离开-加入到实际产品的商品表可用.
从所有事物的主列表开始，然后将其加入到其他事物中。然后，将其保留在另一个(通过NULL)中没有的位置。
select
AllPossible.ProductID,
p.name ProductName,
AllPossible.warehouseID,
w.name WarehouseName,
p.brandID,
b.name BrandName
from
( select
p1.id productID,
w1.id warehouseID
from
product p1
cross join warehouse w1 ) AllPossible
LEFT JOIN goods g
on AllPossible.productID = g.productID
AND AllPossible.warehouseID = g.warehouseID
-- Now, re-join for product, warehouse and brand so we can get
-- the details of where it is NOT found...
-- I can get the descriptions from these three base on the"AllPossible"
JOIN product p
on AllPossible.productID = p.id
JOIN brand b
on p.brandid = b.id
JOIN warehouse w
on AllPossible.warehouseID = w.id
where
-- and only get records where it is NOT found in the goods table via NULL
g.id IS NULL页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66233397
复制相关文章
相似问题
腾讯云开发者
