MySQL - 连接多个映射表并计算具有不同映射条件的记录

Question

这是我尝试编写 MySQL 查询的第三天。进行了大量的搜索，但仍然没有达到预期的效果。我会尽量简化表格

系统有tkr_restaurants表:

restaurant_id | restaurant_name
1             | AA
2             | BB
3             | CC

每个餐厅都分配有一个部门(tkr_divisions 表):

division_id | restaurant_id | division_name
1           | 1             | AA-1
2           | 1             | AA-2
3           | 2             | BB-1

然后，tkr_meals_to_restaurants_divisions 表中有餐食，其中每顿餐食可以分配(映射)到整个餐厅和/或特定部门。如果餐食映射到餐厅，则所有餐厅的部门都应该看到它。如果膳食映射到部门，则只有特定部门应该看到它。

meal_id | mapped_restaurant_id | mapped_division_id
1       | 1                    | NULL
2       | NULL                 | 1
3       | NULL                 | 2

我需要根据用户权限显示餐厅列表以及映射到它的餐食数量。

示例 1:如果用户有权访问整个 restaurant_id 1 和 restaurant_3(并且没有特定部门)，则列表应为:

AA | 3
CC | 0

(因为用户可以访问映射到餐厅 1 + 所有部门和餐厅 3 + 所有部门的餐食(即使餐厅 3 没有映射部门/餐食))

示例 2:如果用户仅有权访问 division_id 1，则列表应为:

AA | 1

(因为用户只能访问映射到分区 1 的餐食)。

我能得到的最接近的查询是:

示例1:

    SELECT *,
    (SELECT COUNT(DISTINCT meal_id) 
    FROM 
        tkr_meals_to_restaurants_divisions 
    WHERE 
 tkr_meals_to_restaurants_divisions.mapped_restaurant_id=tkr_restaurants.restaurant_id 
    OR tkr_meals_to_restaurants_divisions.mapped_division_id=tkr_divisions.division_id)AS total_meals
    FROM 
        tkr_restaurants 
    LEFT JOIN 
        tkr_divisions 
        ON tkr_restaurants.restaurant_id=tkr_divisions.restaurant_id 
    WHERE 
        tkr_restaurants.restaurant_id IN (1, 3) 
        OR tkr_restaurants.restaurant_id IN (
            SELECT restaurant_id 
            FROM tkr_divisions 
            WHERE division_id IN (NULL)
        ) 
    GROUP BY 
       tkr_restaurants.restaurant_id 
    ORDER BY 
        tkr_restaurants.restaurant_name

但是，结果是:

AA | 2
CC | 0

我相信我使这个查询过于复杂化，但我编写的所有更简单的查询都会产生更不准确的结果。

Answer 1

这个查询怎么样:

SELECT 
FROM tkr_restaurants AS a 
JOIN tkr_divisions AS b 
ON a.restaurant_id = b.restaurant_id
LEFT OUTER JOIN tkr_meals_to_restaurants_divisions AS c 
ON (c.mapped_restaurant_id = a.restaurant_id OR c.mapped_division_id = b.division_id)

作为四号基地你的进一步工作。它将所有信息合并到一张表中。如果您添加例如这个:

WHERE a.restaurant_id IN (1, 3)

结果将是

| restaurant_id | restaurant_name | division_id | restaurant_id | division_name | meal_id | mapped_restaurant_id | mapped_division_id |
|---------------|-----------------|-------------|---------------|---------------|---------|----------------------|--------------------|
|             1 |              AA |           1 |             1 |          AA-1 |       1 |                    1 |             (null) |
|             1 |              AA |           2 |             1 |          AA-2 |       1 |                    1 |             (null) |
|             1 |              AA |           1 |             1 |          AA-1 |       2 |               (null) |                  1 |
|             1 |              AA |           2 |             1 |          AA-2 |       3 |               (null) |                  2 |

只需使用 COUNT(DISTINCT c.meal_id) 计算不同的餐食 ID，并根据餐厅名称获取示例 2 的 AA: 3

我使用了sqlfiddle:http://sqlfiddle.com/#!9/fa2b78/18/0

[编辑]

将JOIN tkr_divisions AS b更改为LEFT OUTER JOIN tkr_divisions AS b

将 SELECT * 更改为 SELECT a.restaurant_name, COUNT(DISTINCT c.meal_id)

在末尾添加一个GROUP BY a.restaurant_name。

更新 SQL Fiddle(新链接)