数据如下:table
age num men women
1 175 93 82
2 144 77 67
3 161 88 73
4 150 86 64
5 161 85 76
6 126 66 60
7 171 98 73
8 181 110 71
9 162 88 74
10 137 84 53
11 135 76 59
12 145 75 70
13 138 74 64
14 159 83 76
15 163 95 68
16 144 76 68
17 140 78 62
比如像这样的数据,我现在要统计 age (年龄)为0~10之间 11~20 这样之间的年龄段的男性和女性分别多少,还有总人数多少!
请教高手们用一个sql语句怎么写出来???
age num men women
1 175 93 82
2 144 77 67
3 161 88 73
4 150 86 64
5 161 85 76
6 126 66 60
7 171 98 73
8 181 110 71
9 162 88 74
10 137 84 53
11 135 76 59
12 145 75 70
13 138 74 64
14 159 83 76
15 163 95 68
16 144 76 68
17 140 78 62
比如像这样的数据,我现在要统计 age (年龄)为0~10之间 11~20 这样之间的年龄段的男性和女性分别多少,还有总人数多少!
请教高手们用一个sql语句怎么写出来???
sum(case when age between 10 and 20 then 1 else 0 end ) as [10-20]
from ta
group by sex
....
..
from tb
sum(case when age between 0 and 10 then men else 0 end) as men,
sum(case when age between 0 and 10 then women else 0 end) as women
from
tb
SUM(NUM) AS [总人数],
SUM(CASE WHEN AGE BETWEEN 0 AND 10 THEN men ELSE 0 END) AS [men0-10],
SUM(CASE WHEN AGE BETWEEN 0 AND 10 THEN women ELSE 0 END) AS [women0-10],
...
FROM
TB
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 15:31:11
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (age int,num int,men int,women int)
insert into @tb
select 1,175,93,82 union all
select 2,144,77,67 union all
select 3,161,88,73 union all
select 4,150,86,64 union all
select 5,161,85,76 union all
select 6,126,66,60 union all
select 7,171,98,73 union all
select 8,181,110,71 union all
select 9,162,88,74 union all
select 10,137,84,53 union all
select 11,135,76,59 union all
select 12,145,75,70 union all
select 13,138,74,64 union all
select 14,159,83,76 union all
select 15,163,95,68 union all
select 16,144,76,68 union all
select 17,140,78,62select
case when age between 1 and 10 then '1-10'
when age between 11 and 20 then '11-20' end,
man=sum(men),
women=sum(women),
num=sum(men)+sum(women)
from @tb
group by
case when age between 1 and 10 then '1-10'
when age between 11 and 20 then '11-20' end man women num
----- ----------- ----------- -----------
1-10 875 693 1568
11-20 557 467 1024(2 行受影响)
where age between 0 and 10
union all
select age='11-20',man=sum(men), women=sum(women), num=sum(num) from tb
where age between 11 and 20