mysql - 嵌套旋转

Question

上次您教我使用 SELECT CASE 进行旋转，这非常有帮助。这次我需要一些更高级的帮助。

情况如下:

我有相同的旧死亡记录数据库；存储原因的列包含所有原因的值，我使用 select case 将其转换为 n 个原因的 n 列，如下所示

SELECT anno, cod_comune,
SUM(CASE sex WHEN 'M' THEN 1 ELSE 0 END) AS M,
SUM(CASE sex WHEN 'F' THEN 1 ELSE 0 END) AS F,
SUM(CASE cod_tit WHEN 'I' THEN 1 ELSE 0 END) AS dis_I,
SUM(CASE cod_tit WHEN 'II' THEN 1 ELSE 0 END) AS dis_II ,
SUM(CASE cod_tit WHEN 'III' THEN 1 ELSE 0 END) AS dis_III,
...
FROM casi 
WHERE cod_comune>40000 AND cod_comune<200000 AND cod_comune NOT LIKE '%00'
GROUP BY anno, cod_comune, sex
ORDER BY anno, cod_comune, age;

通过这样做，我获得了每个城市的 2 行，一行为男性，一行为女性，以及每种原因造成的死亡人数。

也就是说，我得到了这样的东西

+------+------------+------+------+-------+--------+---------+
| anno | cod_comune | M    | F    | dis_I | dis_II | dis_III |
+------+------------+------+------+-------+--------+---------+
| 2003 |      41001 |    0 |   19 |     0 |      5 |       0 |
| 2003 |      41001 |   20 |    0 |     0 |      7 |       0 |
| 2003 |      41002 |   12 |    0 |     0 |      3 |       0 |
| 2003 |      41002 |    0 |   16 |     0 |      6 |       0 |
| 2003 |      41003 |    8 |    0 |     0 |      1 |       0 |
| 2003 |      41003 |    0 |    6 |     0 |      2 |       0 |
| ...  |      ...   |  ... |  ... |   ... |    ... |     ... |

相反，我的教授说，如果每个城镇只有 1 行，并且无论如何都保留性别区别，那就更好了。

因此，我尝试将与原因相关的列加倍，例如 CauseI_m 和 CauseI_f。我给出了两个条件来选择大小写，但它不起作用，其中原因我全是零。

SELECT anno, cod_comune,
SUM(CASE sex WHEN 'M' THEN 1 ELSE 0 END) AS M,
SUM(CASE sex WHEN 'F' THEN 1 ELSE 0 END) AS F,
SUM(CASE sex WHEN 'M' AND cod_tit='I' THEN 1 ELSE 0 END) AS dis_Im,
SUM(CASE sex WHEN 'M' AND cod_tit='II' THEN 1 ELSE 0 END) AS dis_IIm ,
SUM(CASE sex WHEN 'M' AND cod_tit='III' THEN 1 ELSE 0 END) AS dis_IIIm,
...
SUM(CASE sex WHEN 'F' AND cod_tit='I'  THEN 1 ELSE 0 END) AS dis_If,
SUM(CASE sex WHEN 'F' AND cod_tit='II'   THEN 1 ELSE 0 END) AS dis_IIf ,
SUM(CASE sex WHEN 'F' AND cod_tit='III'   THEN 1 ELSE 0 END) AS dis_IIIf,
...
FROM casi 
WHERE cod_comune>40000 AND cod_comune<200000 AND cod_comune NOT LIKE '%00'
GROUP BY anno, cod_comune, sex
ORDER BY anno, cod_comune, age;

我从这个查询中得到

+------+------------+------+------+--------+---------+--------+---------+
| anno | cod_comune | M    | F    | dis_Im | dis_IIm | dis_If | dis_IIf |
+------+------------+------+------+--------+---------+--------+---------+
| 2003 |      41001 |   20 |   19 |      0 |       0 |      0 |       0 |
| 2003 |      41002 |   12 |   16 |      0 |       0 |      0 |       0 |
| 2003 |      41003 |    8 |    6 |      0 |       0 |      0 |       0 |
| 2003 |      41004 |    7 |    5 |      0 |       0 |      0 |       0 |
| 2003 |      41005 |    2 |    5 |      0 |       0 |      0 |       0 |
| ...  |      ...   |  ... |  ... |   ...  |   ...   |    ... |  ...    |

我确信我已命令数据库对性别等于该模式且原因是我指定的情况进行汇总。但有些事情不对劲，我不明白是什么。如果您能给我一点帮助，我会很高兴:)(我希望能很好地解释这个问题，英语仍然是我的第二语言)

Answer 1

我可能是错的，但我认为你应该从分组语句中删除“性别”列:

SELECT anno, cod_comune,
SUM(CASE sex WHEN 'M' THEN 1 ELSE 0 END) AS M,
SUM(CASE sex WHEN 'F' THEN 1 ELSE 0 END) AS F,
SUM(CASE WHEN sex='M' AND cod_tit='I' THEN 1 ELSE 0 END) AS dis_M_I,
SUM(CASE WHEN sex='M' AND cod_tit='II' THEN 1 ELSE 0 END) AS dis_M_II ,
SUM(CASE WHEN sex='M' AND cod_tit='III' THEN 1 ELSE 0 END) AS dis_M_III,
SUM(CASE WHEN sex='F' AND cod_tit='I' THEN 1 ELSE 0 END) AS dis_F_I,
SUM(CASE WHEN sex='F' AND cod_tit='II' THEN 1 ELSE 0 END) AS dis_F_II ,
SUM(CASE WHEN sex='F' AND cod_tit='III' THEN 1 ELSE 0 END) AS dis_F_III,
...
FROM casi 
WHERE cod_comune>40000 AND cod_comune<200000 AND cod_comune NOT LIKE '%00'
GROUP BY anno, cod_comune  -- !!! No sex here !!!
ORDER BY anno, cod_comune, age;

这将停止按性别拆分行。