MySQL动态生成SUM(CASE)列及非空列显示问题求助

原查询与需求 #

原运行正常的SQL查询：

SELECT month(fac_factures.periode_date) as date,
SUM(CASE WHEN fac_articles.famille = '3' THEN totalht END) as 'management',
SUM(CASE WHEN fac_articles.famille = '5' THEN totalht END) as 'conseil',
SUM(CASE WHEN fac_articles.famille = '6' THEN totalht END) as 'audit',
SUM(CASE WHEN fac_articles.famille = '7' THEN totalht END) as 'formation',
SUM(CASE WHEN fac_articles.famille = '4' THEN totalht END) as 'refac'
from fac_facturearticles
LEFT JOIN fac_factures ON fac_facturearticles.facture = fac_factures.facture_id
LEFT JOIN fac_clients ON fac_factures.facture_client = fac_clients.client_id
LEFT JOIN fac_articles ON fac_facturearticles.article = fac_articles.article_id
LEFT JOIN fac_articles_familles ON fac_articles.famille = fac_articles_familles.articlefamille_id
LEFT JOIN fac_articles_natures ON fac_articles.nature = fac_articles_natures.articlenature_id
WHERE totalht is not NULL
group by year(fac_factures.periode_date), month(fac_factures.periode_date)
order by year(fac_factures.periode_date), month(fac_factures.periode_date);

待解决需求：

1. 自动根据fac_articles.famille的所有值（含未来新增）生成对应列，无需手动枚举
2. 仅显示存在非空totalht的fac_articles.famille列，确认现有WHERE子句是否合适

解决方案 #

1. 动态生成列（自动适配famille值） #

MySQL静态SQL无法自动识别新增列，必须通过**动态SQL（Prepared Statement）**实现。以下是存储过程实现方案：

DELIMITER //
CREATE PROCEDURE GetDynamicFamilleTotals()
BEGIN
    DECLARE column_sql VARCHAR(2000);
    -- 自动获取所有存在非空totalht的famille，并拼接SUM(CASE)语句
    SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'SUM(CASE WHEN fac_articles.famille = ''', fa.famille, ''' THEN totalht END) AS `', fa.famille, '`'
        )
    ) INTO column_sql
    FROM fac_facturearticles ffa
    JOIN fac_articles fa ON ffa.article = fa.article_id
    WHERE ffa.totalht IS NOT NULL;

    -- 拼接完整查询语句
    SET @full_sql = CONCAT(
        'SELECT CONCAT(YEAR(ff.periode_date), ''-'', LPAD(MONTH(ff.periode_date), 2, ''0'')) AS date, ',
        column_sql,
        ' FROM fac_facturearticles ffa
        LEFT JOIN fac_factures ff ON ffa.facture = ff.facture_id
        LEFT JOIN fac_clients fc ON ff.facture_client = fc.client_id
        LEFT JOIN fac_articles fa ON ffa.article = fa.article_id
        LEFT JOIN fac_articles_familles faf ON fa.famille = faf.articlefamille_id
        LEFT JOIN fac_articles_natures fan ON fa.nature = fan.articlenature_id
        WHERE ffa.totalht IS NOT NULL
        GROUP BY YEAR(ff.periode_date), MONTH(ff.periode_date)
        ORDER BY YEAR(ff.periode_date), MONTH(ff.periode_date);'
    );

    -- 执行动态SQL
    PREPARE stmt FROM @full_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用存储过程获取结果：

CALL GetDynamicFamilleTotals();

2. 过滤非空totalht的famille列 #

现有WHERE totalht IS NOT NULL是筛选行的，仅保留存在有效totalht的记录，但无法直接筛选列。

上述动态SQL方案中，在拼接列时已经通过WHERE ffa.totalht IS NOT NULL过滤掉了所有无有效totalht的famille值，生成的列均为存在有效数据的列，完全符合需求。

若需保留部分famille列无数据的日期行，可保留原WHERE子句；若仅需保留至少一个famille有数据的日期行，当前GROUP BY逻辑已自动处理此类情况。

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