多对多和多对多交叉点 [英] many-to-many and many-to-many intersections

问题描述

说我有一个数据库，其中包含人员，杂货店和您可以在商店中购买的商品，例如:

Say I have a database that has people, grocery stores, and items you can buy in the store, like so:

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  |
-----------------                          ------------------

我还有一个表格可以跟踪哪些商店出售哪些商品:

I have an additional table which keep track of which stores sell what:

Inventory
--------------------
| store_id| food_id|
--------------------
| 1       | 1      |
| 1       | 2      |
| 2       | 1      |
| 3       | 1      |
| 3       | 2      |
| 3       | 3      |
--------------------

我还有另一个桌子上有购物清单

And I have another table that has shopping lists on it

Lists
---------------------
| person_id| food_id|
---------------------
| 1        | 1      |
| 1        | 2      |
| 1        | 3      |
| 2        | 1      |
| 2        | 3      |
---------------------

我的问题是，给定一个人或他们的ID，找出他们可以去的商店的最佳方法是什么，以便他们将所有物品都列入清单.在MySQL中是否有针对这些类型的计算的模式?

My question is, given a person, or their id, what is the best way to figure out what stores they can go to so they will get everything on their list. Is there a pattern for these types of computations in MySQL?

我的尝试(非常丑陋和凌乱)类似于:

My attempt (very ugly and messy) is something like:

-- 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子句上的子查询，以计算每个人在列表上的项数.试试看，

If I were to solved the problem, I'll join the four tables with their linking column (specifically the foreign keys) then a subquery on the HAVING clause to count the number of items on the list for each person. Give this a try,

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
     )

这篇关于多对多和多对多交叉点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！