题解 | #小破站9月新会员人数#

重点1:date_format函数的使用 重点2：sql中的if用法，if(True/False,condition1,condition2),为真 的时候count1，否则不计数

SELECT

    COUNT(IF(login_time BETWEEN '7:00:00' AND '9:00:00' OR login_time BETWEEN '18:00:00' AND '20:00:00', 1, NULL)) AS '通勤',

    COUNT(IF(login_time BETWEEN '11:00:00' AND '13:00:00', TRUE, NULL)) AS '午休',

    COUNT(IF(login_time BETWEEN '22:00:00' AND '24:00:00' OR login_time BETWEEN '00:00:00' AND '1:00:00', 1, NULL)) AS '临睡'

FROM 
    login_tb
WHERE 
    DATE_FORMAT(login_date, '%Y-%m')='2021-07';