语句列表中包含多列的 MySQL CASE 语句

Question

我有这个查询，我知道它不起作用，但我将其保留为伪代码以帮助解释我在做什么。我正在尝试按过去一周的星期几从预订表中获取“预订”和“销售”总数。因此，Mon1B = 周一的预订量，Mon1S = 周一的销售额。

SELECT  
   CASE WEEKDAY(b.created)
   WHEN 0 THEN (SELECT COUNT(uuid) as Mon1B, SUM(amount) as Mon1S)
   WHEN 1 THEN (SELECT COUNT(uuid) as Tue1B, SUM(amount) as Tue1S)
   WHEN 2 THEN (SELECT COUNT(uuid) as Wed1B, SUM(amount) as Wed1S)
   WHEN 3 THEN (SELECT COUNT(uuid) as Thu1B, SUM(amount) as Thu1S)
   WHEN 4 THEN (SELECT COUNT(uuid) as Wed1B, SUM(amount) as Wed1S)
   WHEN 5 THEN (SELECT COUNT(uuid) as Wed1B, SUM(amount) as Wed1S)
   WHEN 6 THEN (SELECT COUNT(uuid) as Wed1B, SUM(amount) as Wed1S)
END CASE
FROM Bookings b
WHERE b.created > '#week1Start#' and b.created <= '#week1End#'

如何在 MySQL 中完成这样的事情？

Answer 1

是的，但是case只能返回一个值。你可以这样做:

SELECT sum(CASE when WEEKDAY(b.created) = 0 then 1 else 0 end) as Mon1b,
       sum(case when weekday(b.created) = 0 then amount else 0 end) as Mon1S,
       ...
FROM Bookings b
WHERE b.created > '#week1Start#' and b.created <= '#week1End#'

不过，您可能会发现 7 行更容易:

select WEEKDAY(b.created), count(*) as cnt, sum(amount) as amt
from Bookings b
WHERE b.created > '#week1Start#' and b.created <= '#week1End#'
group by WEEKDAY(b.created)
order by 1