mysql 分组分解为多行,MySQL - 按多行分组

I have an online survey for my users, and every time user answers a survey, I would capture their details in a "survey_stats" table like this -

| id | user_id | survey_id | key | value |

|---------|---------|-----------|--------------|---------|

| 1 | 10 | 99 | gender | male |

| 2 | 10 | 99 | age | 32 |

| 3 | 10 | 99 | relationship | married |

| 4 | 11 | 99 | gender | female |

| 5 | 11 | 99 | age | 27 |

| 6 | 11 | 99 | relationship | single |

In other words, when a user answers a survey, I would capture user's attributes (gender, age, relationship) in 3 rows (each row for each separate attribute about the user). The reason why we are keeping this way is if we want to expand the user attributes in future, we can just add more records per survey, instead of modifying the survey_stats table structure.

I would like to construct a SQL to generate report like this:

| age_group | male | female |

|-----------|-------|---------|

| 13 - 17 | 10 | 7 |

| 18 - 24 | 25 | 10 |

| 25 - 34 | 30 | 11 |

Would really appreciate some help to show me how to write a single SQL statement to do so.

FYI - I'm using MySQL. The said project is a Ruby on Rails app.

Many thanks.

解决方案select

case when YT2.age between 13 and 17 then "13 - 17"

when YT2.age bewteen 18 and 24 then "18 - 24"

when YT2.age between 25 and 35 then "25 - 34"

else "35 and over" end as AgeGroup,

Sum( if( YT1.value = "male", 1, 0 )) as MaleFlag,

SUM( if( YT1.value = "female", 1, 0 )) as FemaleFlag

from

YourTable YT1

JOIN YourTable YT2

ON YT1.user_id = YT2.user_ID

AND YT1.survey_id = YT2.survey_id

AND YT2.key = "age"

where

YT1.Key = "gender"

group by

AgeGroup