SQL -在不使用UNION的情况下从同一个表中选择两次行

Question

我有桌子：

meal_categories
---------------------------
category_id | category_name
1           | Hot Drinks
2           | Cold Drinks
3           | Alcohol Drinks


meals
---------------------------------
meal_id | category_id | meal_name
1       | 1           | Tea
2       | 2           | Sprite
3       | 3           | Vodka + Sprite


meals_to_meals
------------------------
meal_id | mapped_meal_id
3       | 2

正如你所看到的，任何一餐都可以有任意数量的其他食物(来自同一/其他类别)在里面。

假设我需要显示一个类别的3餐(Vodka + Sprite)和任何子类别(以及其中使用过的餐数)(如果有它的话)。因此，结果应该是：

category_name  | total_meals
Alcohol Drinks | 1
Cold Drinks    | 1

如果有人搞不懂这些数字是什么意思：第三餐是酒精饮料本身，所以我们有1和第3餐也有1餐从类别冷饮里面，所以我希望计算是有意义的。

对我来说，最大的挑战是选择分餐作为单独的一排。如果我使用JOIN查询，它将只返回一行(即使它正确地返回映射的餐id )：

SELECT * FROM meals 
JOIN meals_to_meals
ON meals.meal_id=meals_to_meals.meal_id AND meals.meal_id=3

因此，经过大量搜索，我决定使用UNION：

SELECT aaa.* FROM meals as aaa
JOIN meals_to_meals
ON aaa.meal_id=meals_to_meals.meal_id
GROUP BY aaa.meal_id
UNION ALL
SELECT meals.* FROM meals_to_meals
JOIN meals
ON meals_to_meals.mapped_meal_id=meals.meal_id

这一次正确地返回+映射的膳食(作为单独的行)，但是如果我添加进一步的查询来显示类别和其中的餐数，我会得到错误的--使用的SELECT语句有不同数量的列。

我确信，如果我能够在不使用UNION的情况下实现相同的结果，我可以设法显示类别(这样我就可以更容易地理解这个查询)。有什么想法吗？

顺便说一句，这是小提琴，随便玩吧- http://sqlfiddle.com/#!9/64313b/8

Answer 1

UNION ALL已经是最好的方法，只要这只是两个级别(饮料可以由其他饮料组成，但饮料不能由其他饮料本身组成-您需要一个递归查询，而MySQL不提供这个查询)。

以下是对第3餐的查询：

select mc.category_name, count(*) as total_meals
from
(
  select category_id from meals where meal_id = 3
  union all
  select category_id from meals where meal_id in
    (select mapped_meal_id from meals_to_meals where meal_id = 3)
) c
join meal_categories mc using (category_id)
group by mc.category_name
order by mc.category_name;

SQL小提琴：http://sqlfiddle.com/#!9/64313b/12