如何通过循环运行CASE语句优化冗余SQL透视查询

你的这段SQL确实因为重复的SUM(CASE)块显得非常冗余，本质上这是一个行转列的透视查询，要处理1-31号的午餐状态统计。下面给你两种实用的优化方案，不用手动写31次重复代码：

方案1：用MySQL存储过程生成动态SQL #

如果你的数据库是MySQL，可以写一个存储过程，通过循环自动生成所有日期的统计字段，执行后直接得到结果：

DELIMITER //
CREATE PROCEDURE GetMonthlyLunchStatus(IN targetUserId INT)
BEGIN
    DECLARE dayNum INT DEFAULT 1;
    DECLARE sqlQuery VARCHAR(10000) DEFAULT '';
    
    -- 初始化SQL的开头部分
    SET sqlQuery = 'SELECT id, title';
    
    -- 循环生成1到31号的SUM(CASE)语句
    WHILE dayNum <= 31 DO
        SET sqlQuery = CONCAT(sqlQuery, ', SUM(CASE WHEN day = ', dayNum, ' THEN lunchStatus ELSE 0 END) ''', dayNum, '''');
        SET dayNum = dayNum + 1;
    END WHILE;
    
    -- 拼接SQL的剩余部分
    SET sqlQuery = CONCAT(sqlQuery, ' 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 = ', targetUserId, ') l ON m.title = l.month ) as s GROUP BY title ORDER BY id');
    
    -- 执行动态生成的SQL
    PREPARE stmt FROM sqlQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用的时候只需要调用存储过程，传入用户ID即可：

CALL GetMonthlyLunchStatus(134);

方案2：在应用层生成SQL（更灵活） #

如果不想在数据库端写存储过程，也可以在你的应用代码（比如Python、Java、PHP等）里通过循环生成SQL语句，举个Python的例子：

def generate_lunch_sql(user_id):
    base_sql_start = """
    SELECT id, title
    """
    day_fields = []
    for day in range(1, 32):
        day_fields.append(f"SUM(CASE WHEN day = {day} THEN lunchStatus ELSE 0 END) '{day}'")
    
    base_sql_end = f"""
    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 = {user_id}
        ) l ON m.title = l.month
    ) as s
    GROUP BY title
    ORDER BY id
    """
    full_sql = base_sql_start + ",\n".join(day_fields) + base_sql_end
    return full_sql

# 使用示例
sql = generate_lunch_sql(134)
print(sql)

这段代码会自动帮你生成和原始SQL功能完全一致，但代码更简洁的查询语句，你可以直接执行生成后的SQL。

额外提示 #

如果需要适配不同月份的天数（比如2月最多28/29天），可以在循环的时候根据目标月份动态调整结束的数字，但如果你的需求是固定展示1-31列（没有的日期显示0），那当前的方案就足够了。

内容的提问来源于stack exchange，提问作者Azima