MySQL - 按多行分组 [英] MySQL - Group by multiple rows

问题描述

我为我的用户进行了在线调查，每次用户回答调查时，我都会在survey_stats表中记录下他们的详细信息 -

p> | id | user_id | survey_id |键|值|
| --------- | --------- | ----------- | ------------- - | --------- |
| 1 | 10 | 99 |性别|男性|
| 2 | 10 | 99 |年龄| 32 |
| 3 | 10 | 99 |关系|已婚|
| 4 | 11 | 99 |性别|女| |
| 5 | 11 | 99 |年龄| 27 |
| 6 | 11 | 99 |关系|单| |


换句话说，当用户回答调查时，我会捕获用户的属性（性别，年龄，关系）分为3行（关于用户的每个单独属性每行）。我们保持这种方式的原因在于，如果我们想在将来扩展用户属性，我们可以为每个调查添加更多记录，而不是修改survey_stats表结构。

我想构建一个SQL来生成像这样的报告：

  | age_group |男性|女| | 
 | ----------- | ------- | --------- | 
 | 13  -  17 | 10 | 7 | 
 | 18  -  24 | 25 | 10 | 
 | 25  -  34 | 30 | 11 | 
  

真的很感谢一些帮助，告诉我如何编写单个SQL语句来完成这项工作。 p>

仅供参考 - 我正在使用MySQL。这个项目是一个Ruby on Rails应用程序。

非常感谢。

解决方案

 选择
的情况下，当YT2.age在13和17之间，然后是13  -  17
当YT2.age在18和24之间，然后是18  -  24 
当YT2.age在25到35之间，那么25-34
其他35岁以上结束为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
其中
 YT1.Key =gender
 group by 
 AgeGroup 
  

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

这篇关于MySQL - 按多行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！