当内部mysql case语句未显示在结果中时不匹配 [英] not matching when inside mysql case statement is not showing in result

问题描述

我有一个如下的mysql语句

I have an mysql statement as below

SELECT CASE
       WHEN HOUR(created_at) BETWEEN 0 AND 11 THEN 'Morning'
       WHEN HOUR(created_at) BETWEEN 12 AND 15 THEN 'Afternoon'
       WHEN HOUR(created_at) BETWEEN 16 AND 18 THEN 'Evening'
       WHEN HOUR(created_at) BETWEEN 19 AND 24 THEN 'Night'
   END AS session,
   SUM(total) AS `total` FROM `orders`  WHERE (purchase_date between '2014-05-01' and '2014-05-30')
   GROUP BY CASE
         WHEN HOUR(created_at) BETWEEN 0 AND 11 THEN 1
         WHEN HOUR(created_at) BETWEEN 12 AND 16 THEN 2
         WHEN HOUR(created_at) BETWEEN 17 AND 18 THEN 3
         WHEN HOUR(created_at) BETWEEN 19 AND 24 THEN 4
     END;

我得到这样的输出

+------------+------------+
| session    | total      |
+------------+------------+
| Morning    |   47083.21 |
| Afternoon  | 1124804.51 |
| Evening    |  165643.34 |
| Night      | 1690492.01 |
+------------+------------+

但是，当没有早上的条目时，输出结果中缺少早上"行，但是我想要的行是早上，但总数为0. 请帮助我如何实现同样的目标

But when there are no entries for morning then the output is missing the Morning row in results but I want an row with morning but total as 0. please help me how to achieve the same

Expected output

+------------+------------+
| session    | total      |
+------------+------------+
| Morning    |          0 |
| Afternoon  |   14804.51 |
| Evening    |   16643.34 |
| Night      |   19492.01 |
+------------+------------+

实际输出没有早晨行

Actual output

+------------+------------+
| session    | total      |
+------------+------------+
| Afternoon  | 1124804.51 |
| Evening    |  165643.34 |
| Night      | 1690492.01 |
+------------+------------+

如果能提供任何帮助或提示来解决此问题，我将不胜感激

I would greatly appreciate if Any kind of help or hint is given to solve this problem

推荐答案

添加create table cal (hours int not null).填写0到23(没有24小时吗?).然后做

Add create table cal (hours int not null). Fill it with 0 to 23 (there is no 24 hour?). Then do

SELECT CASE
   WHEN cal.hours BETWEEN 0 AND 11 THEN 'Morning'
   WHEN cal.hours BETWEEN 12 AND 15 THEN 'Afternoon'
   WHEN cal.hours BETWEEN 16 AND 18 THEN 'Evening'
   WHEN cal.hours BETWEEN 19 AND 24 THEN 'Night'
END AS session
,sum(coalesce(total, 0)) from
(select  created_at, total from orders 
 where purchase_date between '2014-05-01' and '2014-05-30') T1
 right outer  join cal on (cal.hours = hour(T1.created_at))
group by CASE
   WHEN cal.hours BETWEEN 0 AND 11 THEN 'Morning'
   WHEN cal.hours BETWEEN 12 AND 15 THEN 'Afternoon'
   WHEN cal.hours BETWEEN 16 AND 18 THEN 'Evening'
   WHEN cal.hours BETWEEN 19 AND 24 THEN 'Night' END;

这篇关于当内部mysql case语句未显示在结果中时不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！