我有两个表在我的数据库(第一个名字- game_table和第二个名字- attendee_table):
/ ---game_table-- ' / -- Attendee_table -- '
| g_id | g_name | | a_id | g_id | a_name | a_age |
| 101 | football | | 1 | 101 | Santosh | 31 |
| 102 | Cricket | | 2 | 101 | Parveen | 35 |
| 102 | Hockey | | 3 | 101 | Ram | 15 |
| 4 | 101 | Radhe | 10 |
| 5 | 101 | Salim | 31 |
| 6 | 101 | sandeep | 25 |
| 7 | 101 | Rahul | 40 |
我想要以下结果:
| age_break_up | Count |
| 0 - 18 years | 2 |
| 19 - 25 years | 1 |
| 26 - 40 years | 4 |
| 41+ years | 0 |
所以我使用以下查询:
mysqli_query($con, "SELECT f.a_age, COUNT(f.a_age) FROM game_table t INNER JOIN attendee_table f ON t.g_id = f.g_id GROUP BY f.a_age")
但是这个查询返回以下输出:
| a_age | Count |
| 10 | 1 |
| 15 | 1 |
| 25 | 1 |
| 31 | 2 |
| 35 | 1 |
| 40 | 1 |
那么我怎么把我的结果像下面这样:
| age_break_up | Count |
| 0 - 18 years | 2 |
| 19 - 25 years | 1 |
| 26 - 40 years | 4 |
| 41+ years | 0 |
如果没有分割组的模式(例如18年的组不是这种情况)，那么我认为你必须使用条件分组，这可以使用CASE EXPRESSION:
SELECT CASE WHEN f.a_age < 19 THEN '0 - 18 years'
WHEN f.a_age between 19 and 25 THEN '19 - 25 years'
WHEN f.a_age between 26 and 40 THEN '26 - 40 years'
ELSE '41+ years'
end as age_break_up,
COUNT(f.a_age)
FROM game_table t
INNER JOIN attendee_table f
ON t.g_id = f.g_id
GROUP BY CASE WHEN f.a_age < 19 THEN '0 - 18 years'
WHEN f.a_age between 19 and 25 THEN '19 - 25 years'
WHEN f.a_age between 26 and 40 THEN '26 - 40 years'
ELSE '41+ years'
END
可以使用select case
mysqli_query($con, "SELECT
case when f.a_age between 0 and 18 then '0 - 18 years'
when f.a_age between 19 and 25 then '19 - 25 years'
when f.a_age between 26 and 40 then '26 - 40 years'
else '26 - 40 years'
end as age_break_up
, COUNT(f.a_age)
FROM game_table t
INNER JOIN attendee_table f ON t.g_id = f.g_id
GROUP BY case when f.a_age between 0 and 18 then '0 - 18 years'
when f.a_age between 19 and 25 then '19 - 25 years'
when f.a_age between 26 and 40 then '26 - 40 years'
else '26 - 40 years'
end ;")
表game_table对于您的查询目的似乎没有发挥重要作用。您必须使用内联年龄"桶"表作为查询的第一个表，然后对其使用LEFT JOIN attendee_table，以便即使对于没有相关记录的年龄桶也可以返回一行:
SELECT CASE t1.age_bucket
WHEN 1 THEN '0 - 18 years'
WHEN 2 THEN '19 - 25 years'
WHEN 3 THEN '26 - 40 years'
ELSE '41+ years'
END AS age_break_up,
COUNT(t2.a_age)
FROM (SELECT 1 AS age_bucket UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4) AS t1
LEFT JOIN atendee_table AS t2 ON t1.age_bucket = IF(t2.a_age BETWEEN 0 AND 18, 1,
IF(t2.a_age BETWEEN 19 AND 25, 2,
IF(t2.a_age BETWEEN 26 AND 40, 3, 4)))
GROUP BY IF(a_age BETWEEN 0 AND 18, 1,
IF(a_age BETWEEN 19 AND 25, 2,
IF(a_age BETWEEN 26 AND 40, 3, 4)))