mysql 使用嵌套查询和COUNT函数

wwtsj6pe  于 2023-05-28  发布在  Mysql
关注(0)|答案(2)|浏览(221)

我正在使用一个SQL表来记录每天给学生提供的饭菜数量。该表的结构如下:
| ID_学生|用餐日期|膳食类型|
| - -----|- -----|- -----|
| 一个|2023-05-21 2023-05-21 2023-05-21|一个|
| 2| 2023-05-21 2023-05-21 2023-05-21|一个|
| 一个|2023-05-21 2023-05-21 2023-05-21| 2|
| 2| 2023-05-21 2023-05-21 2023-05-21| 2|
| 一个|2023-05-22 2023-05-22|一个|
| 一个|2023-05-23 - 2023-05-23| 3|
MealType列指定膳食类型:

  • 1 =早餐
  • 2 =午餐
  • 3 =晚餐

因此,每个学生每天最多可以有3个记录。
我现在尝试使用表“Meals”中的数据创建发票。为此,我需要一个SQL查询,它可以生成给定月份中每天的早餐、午餐和晚餐的总数。根据上面的表格,它应该看起来像这样:
| 用餐日期|早餐|午餐|晚餐|
| - -----|- -----|- -----|- -----|
| 2023-05-21 2023-05-21 2023-05-21| 2| 2| 0|
| 2023-05-22 2023-05-22|一个|0| 0|
| 2023-05-23 - 2023-05-23| 0| 0|一个|
我假设嵌套查询是这里最好的方法,所以这是我所做的:

SELECT MealDate, BreakfastCount, LunchCount, DinnerCount FROM 
(
    SELECT MealDate, LunchCount, DinnerCount, COUNT(*) AS "BreakfastCount" FROM 
    (
        SELECT MealDate, MealType, DinnerCount, COUNT(*) AS "LunchCount" FROM
        (
            SELECT MealDate, MealType, COUNT(*) AS "DinnerCount" FROM Meals
            WHERE MealType=3 AND MealDate LIKE '2023-05-%'
            GROUP BY MealDate
        ) Dinners
        WHERE MealType=2 AND MealDate LIKE '2023-05-%'
        GROUP BY MealDate
    ) Lunches
    WHERE MealType=1 AND MealDate LIKE '2023-05-%'
    GROUP BY MealDate
) Breakfasts
GROUP BY MealDate

问题是,结果总是空的。我该怎么做?单个查询本身会产生预期的结果,但嵌套会破坏它。我怀疑它与WHERE子句中的MealTypes有关,但我不知道如何将COUNT(*)函数限制为特定的膳食类型。

h9vpoimq

h9vpoimq1#

你的方法是好的,虽然应该调整一点,使其工作。首先将条件从WHERE子句移到聚合函数(条件聚合)。然后你会注意到,你总是在同一个领域分组:您可以在一个查询中提取所有计数聚合。
如果你想过滤你的记录到2023年5月,而不是LIKE,最好使用日期函数(如YEARMONTH),或者使用与本月第一天的日期比较。

SELECT MealDate, 
       COUNT(CASE WHEN MealType = 1 THEN ID_Student END) AS Breakfasts,
       COUNT(CASE WHEN MealType = 2 THEN ID_Student END) AS Lunches,
       COUNT(CASE WHEN MealType = 3 THEN ID_Student END) AS Dinners
FROM tab
GROUP BY MealDate
WHERE YEAR(MealDate) = 2023 AND MONTH(MealDate) = 5
# WHERE MealDate BETWEEN '2023-05-01' AND '2023-06-01'

输出

| 用餐日期|早餐|午餐|晚餐|
| - -----|- -----|- -----|- -----|
| 2023-05-21 2023-05-21 2023-05-21| 2| 2| 0|
| 2023-05-22 2023-05-22|一个|0| 0|
| 2023-05-23 - 2023-05-23| 0| 0|一个|
查看演示here

7hiiyaii

7hiiyaii2#

您可以使用条件聚合来执行此操作,如下所示:

select MealDate, COUNT(CASE WHEN MealType = 1 THEN ID_Student END) AS Breakfasts,
                 COUNT(CASE WHEN MealType = 2 THEN ID_Student END) AS Lunches,
                 COUNT(CASE WHEN MealType = 3 THEN ID_Student END) AS Dinners
from mytable
group by MealDate

Demo here

相关问题