成員表
`select age, count(*) as "the number"
from (select floor((to_char(sysdate, 'YYYY') - substr(birth, 1, 4)) / 10) * 10 as age from member)
group by age order by age asc;`
如何獲取資料
當有 10 的資料時。
當沒有 10 的資料時。
即使 10 組中沒有資料，我也想列印。
例如，
年齡/人數
10 / 0
20 / 4
30 / 4
40 / 0
50 / 0
60 / 3
我怎樣才能做到這一點？
uj5u.com熱心網友回復：
你可以試試這個：
db<>小提琴
select grp.age, COALESCE(val."the number", 0) as "the number"
from (
select 10 as age FROM dual UNION ALL
select 20 FROM dual UNION ALL
select 30 FROM dual UNION ALL
select 40 FROM dual UNION ALL
select 50 FROM dual UNION ALL
select 60 FROM dual UNION ALL
select 70 FROM dual UNION ALL
select 80 FROM dual UNION ALL
select 90 FROM dual UNION ALL
select 100 FROM dual
) grp
left join (
select age, count(*) as "the number"
from (select floor((to_char(sysdate, 'YYYY') - substr(birth, 1, 4)) / 10) * 10 as age from member)
group by age
) val ON grp.age = val.age
order by grp.age asc;
uj5u.com熱心網友回復：
僅使用當前年份和出生年份并不總是為您提供正確的年齡。如果您想找到正確的年齡，請使用MONTHS_BETWEEN：
select age,
count(*) as "the number"
from (
select floor(MONTHS_BETWEEN(SYSDATE, birth) / 120)) * 10 as age
from member
)
group by age
order by age asc;
如果您的birth列是字串，則將其轉換為DATEusing TO_DATE（但實際上，您應該修復列資料型別而不是決議字串）：
select age,
count(*) as "the number"
from (
select floor(MONTHS_BETWEEN(SYSDATE, TO_DATE(birth, 'YYYY.MM.DD')) / 120)) * 10 as age
from member
)
group by age
order by age asc;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/481850.html