ibm的一个面试题，大家help下

[zhigangsun]

一个表A(id,age)，要求按照年龄段分类输出各个年龄段的人数(比如1-10,11-20,21-30)。这个sql该怎么写

[paoluo]

Create Table A
(id Int,
age Int)
Insert A Select 1, 12
Union All Select 2, 3
Union All Select 3, 21
Union All Select 4, 5
Union All Select 5, 15
GO
Select
SUM(Case When age Between 1 And 10 Then 1 Else 0 End) As '1- 10',
SUM(Case When age Between 11 And 20 Then 1 Else 0 End) As '11- 20',
SUM(Case When age Between 21 And 30 Then 1 Else 0 End) As '21- 30'
From
A
GO
Drop Table A
--Result
/*
1- 10 11- 20 21- 30
2 2 1
*/

[marco08]

select
sum(case when age between 1 and 10 then 1 else 0 end) as '1-10',
sum(case when age between 11 and 20 then 1 else 0 end) as '11-20',
sum(case when age between 21 and 30 then 1 else 0 end) as '21-30'
from T

[marco08]

--try

select tmp.年龄段, 人数=count(*)
from T
left join
(
select 年龄段='1-10', [min]=1, [max]=10
union all select '11-20', 11, 20
union all select '21-30', 21, 30
)tmp on A.age between tmp.[min] and tmp.[max]
group by tmp.年龄段

[一者仁心]

select age=cast(ceiling(45/10.0) as varchar)+'-'+cast(ceiling(45/10.0)*10 as varchar),sum(age) from a group by ceiling(age/10.0)


应该是这个意思吧 ibm没那么彪吧

[simonhehe]

好不容易来,还没得抢
jf

[nzperfect]

我想应该不正这三个段,还有,都要case也麻烦..

[弘毅致远]

Select
SUM(Case When age Between 1 And 10 Then 1 Else 0 End) As '1- 10',
SUM(Case When age Between 11 And 20 Then 1 Else 0 End) As '11- 20',
SUM(Case When age Between 21 And 30 Then 1 Else 0 End) As '21- 30'
From
A
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
支持鱼的代码。
8错。。

[leo_lesley]

路过~~~~

[dali88888]

鱼的经典.

[mugua604]

IBM???

[---涛声依旧---]

paoluo(一天到晚游泳的鱼)的 不錯

[playwarcraft]

--要是寫活點,可以參考

Create Table A
(id int,
age int)
Insert A Select 1, 12
Union All Select 2, 3
Union All Select 3, 21
Union All Select 4, 5
Union All Select 5, 15


select rtrim(van*10+1)+'--'+rtrim((van+1)*10) as [age],[count]
from
(select (age-1)/10 as van, count(*) as [count] from a group by (age-1)/10) T

drop table a

/*
age count
-------------------------- -----------
1--10 2
11--20 2
21--30 1
*/

[leo_lesley]

jf

[十一月猪]

select sum( case when age between 1 and 10 then 1 else 0 end ) as '1-10' ,
sum( case when age between 11 and 20 then 1 else 0 end ) as '11-20' ,
sum( case when age between 21 and 30 then 1 else 0 end ) as '21-30'
from a

[baggio328]

----create test data
create table employee([id] int , age int)

insert into employee
select 1, 21
union
select 2, 22
union
select 3, 30
union
select 4, 35
union
select 5, 40
union
select 6, 50


create table temp_a
(agestage varchar(20), count_a int)

----SQL


declare @a int
set @a = 0
while @a<20
begin
insert into temp_a
select rtrim(10*@a) + '-' + rtrim(10*(@a+1)-1), count(*)
from employee
where age between (10*@a) and (10*(@a+1)-1)

set @a = @a + 1
end

select * from temp_a

----delete test data
drop table temp_a, employee

-----result


agestage count_a
-------------------- -----------
0-9 0
10-19 0
20-29 2
30-39 2
40-49 1
50-59 1
60-69 0
70-79 0
80-89 0
90-99 0
100-109 0
110-119 0
120-129 0
130-139 0
140-149 0
150-159 0
160-169 0
170-179 0
180-189 0
190-199 0

(20 件処理されました)

---仅供参考

[starsky2006]

declare @A table(id Int,age Int)
Insert @A Select 1, 12
Union All Select 2, 3
Union All Select 3, 21
Union All Select 4, 5
Union All Select 5, 15

Select s1=(case When age Between 1 And 10 Then '1- 10'
When age Between 11 And 20 Then '11- 20'
else '21- 30' end)
,count(1)
From @A
group by (case When age Between 1 And 10 Then '1- 10'
When age Between 11 And 20 Then '11- 20'
else '21- 30' end)
order by s1

[rookie_one]

来晚了：（

[baggio328]

来了就不晚，哈哈

[DigJim]

来了，哈哈

[fzcheng]

頂.又學到了不少