php - 按关联项搜索的 SQL

Question

我有下面两个表，我需要能够按项目搜索以找到 shopping_list_id。此外，我想限制查询，以便它不会带回其他包含其他项目的购物 list 。本质上，我是在检查这是否是用户之前保存的购物 list 。如果有匹配的购物 list 但有额外的项目，下面的查询不会处理，我不知道如何做到这一点。

表格:

购物 list

shopping_list_id
user
shopping_list_name

shopping_list_item

shopping_list_item_id
shopping_list_id
category_id
qty
qty_unit_id

这个例子有三个项目，但可以有任何数量。我的 PHP 代码根据用户的输入动态生成 SQL 连接和 where 子句。

我有的查询:

SELECT DISTINCT sli.shopping_list_id 
FROM shopping_list_item sli 
JOIN shopping_list sl ON sli.shopping_list_id=sl.shopping_list_id 
JOIN shopping_list_item sli0 on sli.shopping_list_id=sli0.shopping_list_id 
JOIN shopping_list_item sli1 on sli.shopping_list_id=sli1.shopping_list_id 
JOIN shopping_list_item sli2 on sli.shopping_list_id=sli2.shopping_list_id 
WHERE sl.user_id=:webuser_id 
AND sli0.category_id=3 AND sli0.qty=1 AND sli0.qty_unit_id=3 
AND sli1.category_id=683 AND sli1.qty=1 AND sli1.qty_unit_id=3 
AND sli2.category_id=309 AND sli2.qty=1 AND sli2.qty_unit_id=7

Answer 1

您可以使用 group by/having 方法轻松完成此类型的查询:

select sli.shopping_list_id
from shopping_list_item sli
group by sli.shopping_list_id
having sum(sli.category_id = 3 AND sli.qty = 1 AND sli.qty_unit_id) = 1 and
       sum(sli.category_id = 683 AND sli.qty = 1 AND sli.qty_unit_id = 3) = 1 and
       sum(sli.category_id = 309 AND sli.qty = 1 AND sli.qty_unit_id = 7) = 1 and
       count(*) = 3;