sql - Postgres数组查询

Question

(以下是对我的问题的高度简化描述。公司政策不允许我详细描述实际情况。)

涉及的DB表有:

PRODUCTS:
ID   Name
---------
1    Ferrari
2    Lamborghini
3    Volvo


CATEGORIES:
ID    Name
----------
10    Sports cars
20    Safe cars
30    Red cars

PRODUCTS_CATEGORIES
ProductID    CategoryID
-----------------------
1            10
1            30
2            10
3            20

LOCATIONS:
ID      Name
------------
100     Sports car store
200     Safe car store
300     Red car store
400     All cars r us


LOCATIONS_CATEGORIES:
LocationID    CategoryID
------------------------
100           10
200           20
300           30
400           10
400           20
400           30

请注意，位置与产品不直接相关，仅与类别相关。客户应该能够看到一个位置列表，其中可以提供他们想要购买的产品所属的所有产品类别。所以，例如:

一位顾客想买一辆法拉利。这可以从类别 10 或 30 的商店中获得。这给了我们商店 100、300 和 400，但不是 200。

但是，如果客户想要购买沃尔沃和兰博基尼，则可以从类别 10 和 20 的商店购买。这只给了我们商店 400 个。

另一位客户想购买法拉利和沃尔沃。他们可以从类别 10 + 20(运动和安全)或类别 30 + 20(红色和安全)的商店购买。

我需要的是一个 postgres 查询，它获取大量产品并返回可以找到所有产品的位置。我开始使用数组和 <@ 运算符，但很快就迷路了。下面是一些示例 SQL，它试图获取可以购买法拉利和兰博基尼的商店。它无法正常工作，因为它要求位置满足所有所选汽车所属的所有类别。它仅返回位置 400，但应该返回位置 400 和 100。

SELECT l.* FROM locations l
WHERE 
(SELECT array_agg(DISTINCT(categoryid)) FROM products_categories WHERE productid IN (1,2))
<@
(SELECT array_agg(categoryid) FROM locations_categories WHERE locationid = l.id);

我希望我的描述有意义。

Answer 1

这是查询。您应该插入选定汽车 ID 的列表 pc.ProductId in (1,3)最后你应该更正条件以选择汽车数量所以如果你选择 1 和 3 你应该写 HAVING COUNT(DISTINCT pc.ProductId) = 2如果您选择 3 辆汽车，则必须有 3 辆。HAVING 中的这种情况为您提供条件，即所有汽车都在这些位置:

SELECT Id FROM Locations l
JOIN Locations_Categories lc on l.Id=lc.LocationId
JOIN Products_Categories pc on lc.CategoryId=pc.CategoryID
where pc.ProductId in (1,3)
GROUP BY l.id
HAVING COUNT(DISTINCT pc.ProductId) = 2

Sqlfiddle demo

例如，对于一辆车，它将是:

SELECT Id FROM Locations l
JOIN Locations_Categories lc on l.Id=lc.LocationId
JOIN Products_Categories pc on lc.CategoryId=pc.CategoryID
where pc.ProductId in (1)
GROUP BY l.id
HAVING COUNT(DISTINCT pc.ProductId) = 1

Only Ferrary demo <知识库> Volvo and a Lamborghini demo