在sqlserver中如何使用一条sql语句对年龄进行分段(100分)

F

fjx_jwf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #1

在SQLSERVER2000中有如下表：
id 姓名 年龄
001 aaa 50
002 bbb 20
003 ccc 25
004 ddd 35
005 eee 41
..............
现我想用一条sql语句对年龄进行分段统计人数，这条sql语句应如何写
结果应为:
年龄段 人数
30-42 2
20-30 2
42以上 1

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #2

select olda=case old
when (old>20)and(old<30) then "20-30"
when (old>30)and(old<40) then "30-40"
when old>40 then "40以上"
else "小于20”
end,sum(olda)
from tablename
groupby olda

 

J

jrq

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #3

同意楼上～

 

L

llh_lily

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #4

select AgeName as 年龄段,count(id) as 人数
from
(select id, AgeName=
case
when (Age < 42 and Age >= 30) then '30-42'
when (Age < 30 and Age >= 20) then '20-30'
when (Age >= 42) then '42以上'
end
from Tab1) as TT
group by AgeName

 

F

fjx_jwf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #5

执行不对 提示 by附近有错
select 年龄段=case when (年龄>20)and(年龄<30) then '20-30'
when (年龄>30)and(年龄<40) then '30-40'
when 年龄>40 then '40以上'
else '小于20'
end,count(*)
from zcjy
group by 年龄段

 

L

leozmy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #6

测试通过了。sqlserver不能把别名放在group子句中
select case
when (a>0) and (a<10) then '0-9'
when (a>=10)and(a<20) then '10-19'
when a>=20 then '20---'
end,
count(*)
from table1
group by case
when (a>0) and (a<10) then '0-9'
when (a>=10)and(a<20) then '10-19'
when a>=20 then '20---'
end

 

F

fjx_jwf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #7

to llh_lily:提示as附近有错
to leozmy:正在测试
select AgeName as 年龄段,count(id) as 人数
from
(select 姓名,AgeName=
case
when (年龄< 42 and 年龄>=30) then '30-42'
when (年龄< 30 and 年龄>= 20) then '20-30'
when (年龄>= 42) then '42以上'
end
from zcjy) as tt
group by AgeName

 

L

llh_lily

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #8

你的语句应为：
select AgeName as 年龄段,count(姓名) as 人数
from
(select 姓名,AgeName=
case
when (年龄< 42 and 年龄>=30) then '30-42'
when (年龄< 30 and 年龄>= 20) then '20-30'
when (年龄>= 42) then '42以上'
end
from zcjy) as tt
group by AgeName

 

Y

yitanhg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-10

• #9

llh_lily的方法完全正确

 

S

sanming

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-10

• #10

同意楼上

 

F

fjx_jwf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-30

• #11

非常感谢

 

A

antic_ant

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-30

• #12

完了!

 

F

fjx_jwf

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-07-07

• #13

多人接受答案了。