mysql統計語法

mysql sql

sibulun 2022-08-03 11:38:37 ‧ 1308 瀏覽

分享至

以下為性別和年齡資料表

sex	age
男生	65
男生	40
女生	35
女生	20
男生	55
男生	26
男生	45
查詢男生與女生在各年齡層人數mysql語法？
sex	20-30
---	---
男生	1
女生	1

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2022-08-03 11:58:45

最佳解答

參考~

select sex
,sum(case when age between 1 and 19 then 1 else 0 end) as a
,sum(case when age between 20 and 30 then 1 else 0 end) as b
,sum(case when age between 31 and 40 then 1 else 0 end) as c
,sum(case when age between 41 and 50 then 1 else 0 end) as d
,sum(case when age > 50 then 1 else 0 end) as e
from 表格
group by sex

回應 1
分享
檢舉

sibulun iT邦新手 5 級 ‧ 2022-08-03 12:19:34 檢舉

了解你的意思 ,先顯示各年齡層數字1然後再統計 ,學習了

登入發表回應

wayne0127

iT邦新手 5 級 ‧ 2022-10-01 00:16:32

SELECT
`test`.`sex`,
IFNULL((SELECT COUNT(`sex`) FROM `[table_name]` AS `intab` WHERE `intab`.`sex` = `test`.`sex` AND `intab`.`age` BETWEEN 20 AND 30 GROUP BY `intab`.`sex`), 0) AS '20-30',
IFNULL((SELECT COUNT(`sex`) FROM `[table_name]` AS `intab` WHERE `intab`.`sex` = `test`.`sex` AND `intab`.`age` BETWEEN 30 AND 40 GROUP BY `intab`.`sex`), 0) AS '30-40',
IFNULL((SELECT COUNT(`sex`) FROM `[table_name]` AS `intab` WHERE `intab`.`sex` = `test`.`sex` AND `intab`.`age` BETWEEN 40 AND 50 GROUP BY `intab`.`sex`), 0) AS '40-50',
IFNULL((SELECT COUNT(`sex`) FROM `[table_name]` AS `intab` WHERE `intab`.`sex` = `test`.`sex` AND `intab`.`age` > 50 GROUP BY `intab`.`sex`), 0) AS '50以上'
FROM `[table_name]` AS `test` GROUP BY `test`.`sex`