iT邦幫忙

0

mysql統計語法

  • 分享至 

  • xImage

以下為性別和年齡資料表

sex age
男生 65
男生 40
女生 35
女生 20
男生 55
男生 26
男生 45
查詢男生與女生在各年齡層人數mysql語法?
sex 20-30
--- ---
男生 1
女生 1
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

1
純真的人
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
sibulun iT邦新手 5 級 ‧ 2022-08-03 12:19:34 檢舉

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

0
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`

我要發表回答

立即登入回答