循环内的run case语句

djmepvbi  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(187)

我有一个看起来有点脏的查询:

SELECT id, title, 
    SUM(CASE WHEN day = 1 THEN lunchStatus ELSE 0 END) '1',
    SUM(CASE WHEN day = 2 THEN lunchStatus ELSE 0 END) '2',
    SUM(CASE WHEN day = 3 THEN lunchStatus ELSE 0 END) '3',
    SUM(CASE WHEN day = 4 THEN lunchStatus ELSE 0 END) '4',
    SUM(CASE WHEN day = 5 THEN lunchStatus ELSE 0 END) '5',
    SUM(CASE WHEN day = 6 THEN lunchStatus ELSE 0 END) '6',
    SUM(CASE WHEN day = 7 THEN lunchStatus ELSE 0 END) '7',
    SUM(CASE WHEN day = 8 THEN lunchStatus ELSE 0 END) '8',
    SUM(CASE WHEN day = 9 THEN lunchStatus ELSE 0 END) '9',
    SUM(CASE WHEN day = 10 THEN lunchStatus ELSE 0 END) '10',
    SUM(CASE WHEN day = 11 THEN lunchStatus ELSE 0 END) '11',
    SUM(CASE WHEN day = 12 THEN lunchStatus ELSE 0 END) '12',
    SUM(CASE WHEN day = 13 THEN lunchStatus ELSE 0 END) '13',
    SUM(CASE WHEN day = 14 THEN lunchStatus ELSE 0 END) '14',
    SUM(CASE WHEN day = 15 THEN lunchStatus ELSE 0 END) '15',
    SUM(CASE WHEN day = 16 THEN lunchStatus ELSE 0 END) '16',
    SUM(CASE WHEN day = 17 THEN lunchStatus ELSE 0 END) '17',
    SUM(CASE WHEN day = 18 THEN lunchStatus ELSE 0 END) '18',
    SUM(CASE WHEN day = 19 THEN lunchStatus ELSE 0 END) '19',
    SUM(CASE WHEN day = 20 THEN lunchStatus ELSE 0 END) '20',
    SUM(CASE WHEN day = 21 THEN lunchStatus ELSE 0 END) '21',
    SUM(CASE WHEN day = 22 THEN lunchStatus ELSE 0 END) '22',
    SUM(CASE WHEN day = 23 THEN lunchStatus ELSE 0 END) '23',
    SUM(CASE WHEN day = 24 THEN lunchStatus ELSE 0 END) '24',
    SUM(CASE WHEN day = 25 THEN lunchStatus ELSE 0 END) '25',
    SUM(CASE WHEN day = 26 THEN lunchStatus ELSE 0 END) '26',
    SUM(CASE WHEN day = 27 THEN lunchStatus ELSE 0 END) '27',
    SUM(CASE WHEN day = 28 THEN lunchStatus ELSE 0 END) '28',
    SUM(CASE WHEN day = 29 THEN lunchStatus ELSE 0 END) '29',
    SUM(CASE WHEN day = 30 THEN lunchStatus ELSE 0 END) '30',
    SUM(CASE WHEN day = 31 THEN lunchStatus ELSE 0 END) '31'
FROM
(
    SELECT m.id, m.title, l.month, l.day, l.lunchStatus
FROM `months` m 
LEFT OUTER JOIN 
(SELECT MONTHNAME(issuedDateTime) as month, DAY(issuedDateTime) as day, lunchStatus, userId
 FROM lunch_status 
 WHERE userId = 134) l 
 ON m.title = l.month
    ) as s
    GROUP BY title
    ORDER BY id

我得到了预期的结果集。
但是我想整理和优化脚本,从而在一个整洁的循环中运行长case语句。处理数据库不是我的强项。
非常感谢您的帮助。谢谢。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题