mysql动态交叉表,MySQL中的动态交叉表

I have the following table with the following data

Year | Age Group | Male | Female

2000 | 0 - 25 | 50 | 100

2000 | 26 above | 40 | 75

2001 | 0 - 25 | 150 | 86

2001 | 26 above | 65 | 83

I would like to create a cross tab in the following format

| Male | Female

2000 | 90 | 175

0 - 25 | 50 | 100

26 above | 40 | 75

2001 | 215 | 169

0 - 25 | 150 | 86

26 above | 65 | 83

I will be very grateful for your assistance.

解决方案select * from

(

select year,ageGroup,male,female from t

union all

select year,'' ageGroup,sum(male) male,sum(female) female

from t group by year

) st

order by year,agegroup