MySQL的
假设我有一个拥有人员，杂货店和可在商店购买的物品的数据库，如下所示：
Stores People Foods
----------------- ------------------ ------------------
| id | name | | id | name | | id | name |
----------------- ------------------ ------------------
| 1 | Giant | | 1 | Jon Skeet | | 1 | Tomatoes |
| 2 | Vons | | 2 | KLee1 | | 2 | Apples |
| 3 | Safeway | ------------------ | 3 | Potatoes |
----------------- ------------------
我有一个附加的表格，用于跟踪哪些商店卖出什么：
Inventory
--------------------
| store_id| food_id|
--------------------
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
--------------------
我还有另一张桌上有购物清单
Lists
---------------------
| person_id| food_id|
---------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
---------------------
我的问题是，鉴于一个人或他们的身份证，找出他们可以去哪些商店的最佳方式是什么，以便他们能够将所有商品都列入清单。 在MySQL中有这些类型的计算模式吗？
我的尝试（非常丑陋和混乱）是这样的：
-- Given that _pid is the person_id we want to get the list of stores for.
SELECT stores.name, store_id, num, COUNT(*) AS counter
FROM lists
INNER JOIN inventory
ON (lists.food_id=inventory.food_id)
INNER JOIN (SELECT COUNT(*) AS num
FROM lists WHERE person_id=_pid
GROUP BY person_id) AS T
INNER JOIN stores ON (stores.id=store_id)
WHERE person_id=_pid
GROUP BY store_id
HAVING counter >= num;
谢谢你的时间！
用数据编辑SQL小提琴
如果我要解决这个问题，我将通过它们的链接列（特别是外键）连接这四个表，然后使用HAVING子句中的子查询来计算每个人列表上的项目数。 试试这个，
SET @personID := 1;
SELECT c.name
FROM Inventory a
INNER JOIN Foods b
ON a.food_id = b.id
INNER JOIN Stores c
ON a.store_id = c.id
INNER JOIN Lists d
ON d.food_id = b.id
WHERE d.person_id = @personID
GROUP BY c.name
HAVING COUNT(DISTINCT d.food_id) =
(
SELECT COUNT(*)
FROM Lists
WHERE person_ID = @personID
)
SQLFiddle演示
@JohnWoo：为什么DISTINCT？
另一个...
SET @pid=2;
SELECT store_id, name
FROM inventory
JOIN lists ON inventory.food_id=lists.food_id
JOIN stores ON store_id=stores.id
WHERE person_id=@pid
GROUP BY store_id
HAVING COUNT(*)=(
SELECT COUNT(*)
FROM lists
WHERE person_id=@pid
);
上一篇: mysql
下一篇: Symonfy2 validation: define constraints in yml, and validate an array