mysql - 创建类别mysql后计算百分比和总数

Question

我有这个查询

SELECT 
    trage,
    CASE trage 
        WHEN '<18' THEN SUM(CASE WHEN AGE <18 THEN 1 ELSE 0 END)
        WHEN '18-24' THEN SUM(CASE WHEN AGE >= 18 AND AGE <= 24 THEN 1 ELSE 0 END)
        WHEN '25-34' THEN SUM(CASE WHEN AGE >= 25 AND AGE <= 34 THEN 1 ELSE 0 END)
        WHEN '35-44' THEN SUM(CASE WHEN AGE >= 35 AND AGE <= 44 THEN 1 ELSE 0 END)
        WHEN '45-54' THEN SUM(CASE WHEN AGE >= 45 AND AGE <= 54 THEN 1 ELSE 0 END)
        WHEN '>=55' THEN SUM(CASE WHEN AGE >= 55 THEN 1 ELSE 0 END)
    END Total
FROM 
(   SELECT 
        t_personne.pers_date_naissance, 
        t_personne.pers_date_inscription, 
        TIMESTAMPDIFF(Year, t_personne.pers_date_naissance, t_personne.pers_date_inscription) 
        - CASE 
            WHEN MONTH(t_personne.pers_date_naissance) > MONTH(t_personne.pers_date_inscription) 
             OR (MONTH(t_personne.pers_date_naissance) = MONTH(t_personne.pers_date_inscription) 
             AND DAY(t_personne.pers_date_naissance) > DAY(t_personne.pers_date_inscription))
            THEN 1 ELSE 0 
          END AS AGE
    FROM t_personne
) AS Total
CROSS JOIN
(   SELECT '<18' trage UNION ALL
    SELECT '18-24' UNION ALL
    SELECT '25-34' UNION ALL
    SELECT '35-44' UNION ALL
    SELECT '45-54' UNION ALL
    SELECT '>=55'
)a
GROUP BY trage
ORDER BY FIELD(trage, '<18', '18-24', '25-34', '35-44', '45-54', '>=55')

它给出了一个包含所有类别的两列 trage 和 Total 的表格

如何为“总计”列和“%”添加带有“TOTAL”行的列百分比

感谢您的帮助

Answer 1

目前您还不能执行此操作。为了支持这个MySQL需要窗口函数支持，但它仍然没有。如果您需要这样的功能，我建议切换到 PostgreSQL。

另外看看这个问题:MySql using correct syntax for the over clause