我有一个看起来有点脏的查询:
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语句。处理数据库不是我的强项。
非常感谢您的帮助。谢谢。
暂无答案!
目前还没有任何答案,快来回答吧!