mysql - 在循环内运行 CASE 语句

Question

我有一个看起来有点脏的以下查询：

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 语句。使用数据库不是我的强项。

很感谢任何形式的帮助。谢谢。

标签： mysqlsqlloopscase