求解sql server的一道题目

[HelloWord杰少]

设SQL Server 中的employee表中有AGE字段，用于存放所有员工的年龄信息（int型），试编写一段程序，实用CASE语句，输出全部员工在各个年龄段（每十年位一个年龄段）的人数. - -！分没有多少了，望各位见谅.

[筱筱澄]

select 
sum(case when [age] between  1 and 10 then 1 else 0 end) as [0-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],
sum(case when [age] between 31 and 40 then 1 else 0 end) as [31-40],
sum(case when [age] between 41 and 50 then 1 else 0 end) as [41-50],
sum(case when [age] between 51 and 60 then 1 else 0 end) as [51-60],
sum(case when [age] between 61 and 70 then 1 else 0 end) as [61-70]
from tb

[shoppo0505]

用case? 各个年龄段？
case只能匹配一个条件，怎么能输出所有年龄段？
难不成每个年龄段都case一下？这不是脱裤子放屁么，直接where一下好了。

等高手吧。

[發糞塗牆]

1楼的可以了，而且人的年龄不会太长，所以就算手动，也就到150左右足够了，也就是15列而已，不过她的是一行数据，如果你要一列，那修改一下也是可以的。

[MingoJ]

1楼人才

[Net攻城狮]

select sum(a2.AGE)as compNum from employee as a1,employee as a2
where a1.AGE=a2.AGE+10
group by a2.AGE+10

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
	DROP TABLE tba
END
GO
CREATE TABLE tba
(
	Age INT
)
GO
INSERT INTO tba
SELECT 30 UNION
SELECT 31 UNION
SELECT 43 UNION
SELECT 56 UNION
SELECT 12 UNION
SELECT 67 UNION
SELECT 25 UNION
SELECT 47 UNION
SELECT 1 UNION
SELECT 23 UNION
SELECT 78 UNION
SELECT 29 
GO

SELECT RTRIM(Age / 10 * 10) + '岁-' + RTRIM((Age / 10 + 1) * 10) + '岁' AS Age,COUNT(1) AS Num
FROM tba
GROUP BY Age / 10

Age	Num
0岁-10岁	1
10岁-20岁	1
20岁-30岁	3
30岁-40岁	2
40岁-50岁	2
50岁-60岁	1
60岁-70岁	1
70岁-80岁	1