mysql分段时间查询显示

最新推荐文章于 2022-09-02 11:30:45 发布

慕安凉

最新推荐文章于 2022-09-02 11:30:45 发布

阅读量439

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/lsq_401/article/details/46011275

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

select name,
max(case when date_format(time,'%H:%i') >= '07:00' and date_format(time,'%H:%i') < '07:30' then 1 else 0 end) time1,
max(case when date_format(time,'%H:%i') >= '07:30' and date_format(time,'%H:%i') < '08:00' then 1 else 0 end) time2,
max(case when date_format(time,'%H:%i') >= '08:00' and date_format(time,'%H:%i') < '08:30' then 1 else 0 end) time3,
max(case when date_format(time,'%H:%i') >= '08:30' and date_format(time,'%H:%i') < '09:00' then 1 else 0 end) time4,
max(case when date_format(time,'%H:%i') >= '09:00' and date_format(time,'%H:%i') < '09:30' then 1 else 0 end) time5,
max(case when date_format(time,'%H:%i') >= '09:30' and date_format(time,'%H:%i') < '10:00' then 1 else 0 end) time6,
max(case when date_format(time,'%H:%i') >= '10:00' and date_format(time,'%H:%i') < '10:30' then 1 else 0 end) time7,
max(case when date_format(time,'%H:%i') >= '10:30' and date_format(time,'%H:%i') < '11:00' then 1 else 0 end) time8,
max(case when date_format(time,'%H:%i') >= '11:00' and date_format(time,'%H:%i') < '11:30' then 1 else 0 end) time9,
max(case when date_format(time,'%H:%i') >= '11:30' and date_format(time,'%H:%i') < '12:00' then 1 else 0 end) time10,
max(case when date_format(time,'%H:%i') >= '12:00' and date_format(time,'%H:%i') < '12:30' then 1 else 0 end) time11,
max(case when date_format(time,'%H:%i') >= '12:30' and date_format(time,'%H:%i') < '13:00' then 1 else 0 end) time12,
max(case when date_format(time,'%H:%i') >= '13:00' and date_format(time,'%H:%i') < '13:30' then 1 else 0 end) time13,
max(case when date_format(time,'%H:%i') >= '13:30' and date_format(time,'%H:%i') < '14:00' then 1 else 0 end) time14,
max(case when date_format(time,'%H:%i') >= '14:00' and date_format(time,'%H:%i') < '14:30' then 1 else 0 end) time15,
max(case when date_format(time,'%H:%i') >= '14:30' and date_format(time,'%H:%i') < '15:00' then 1 else 0 end) time16,
max(case when date_format(time,'%H:%i') >= '15:00' and date_format(time,'%H:%i') < '15:30' then 1 else 0 end) time17,
max(case when date_format(time,'%H:%i') >= '15:30' and date_format(time,'%H:%i') < '16:00' then 1 else 0 end) time18,
max(case when date_format(time,'%H:%i') >= '16:00' and date_format(time,'%H:%i') < '16:30' then 1 else 0 end) time19,
max(case when date_format(time,'%H:%i') >= '16:30' and date_format(time,'%H:%i') < '17:00' then 1 else 0 end) time20,
max(case when date_format(time,'%H:%i') >= '17:00' and date_format(time,'%H:%i') < '17:30' then 1 else 0 end) time21,
max(case when date_format(time,'%H:%i') >= '17:30' and date_format(time,'%H:%i') < '18:00' then 1 else 0 end) time22,
max(case when date_format(time,'%H:%i') >= '18:00' and date_format(time,'%H:%i') < '18:30' then 1 else 0 end) time23,
max(case when date_format(time,'%H:%i') >= '18:30' and date_format(time,'%H:%i') < '19:00' then 1 else 0 end) time24,
max(case when date_format(time,'%H:%i') >= '19:00' and date_format(time,'%H:%i') < '19:30' then 1 else 0 end) time25,
max(case when date_format(time,'%H:%i') >= '19:30' and date_format(time,'%H:%i') < '20:00' then 1 else 0 end) time26,
max(case when date_format(time,'%H:%i') >= '20:00' and date_format(time,'%H:%i') < '20:30' then 1 else 0 end) time27

from 表名

注：

name 和 time1--time27 是查询结果集的字段名

%Y 年，4 位
%m 月，数值(00-12)
%d 月的天，数值(00-31)
%H 小时 (00-23)
%i 分钟，数值(00-59)
%s 秒(00-59)