SQL年龄段分组统计

1. --> 测试数据：[tb] 
2. if object_id('[tb]') is not null drop table [tb] 
3. go  
4. create table [tb]([姓名] varchar(1),[部门] varchar(4),[学历] varchar(4),[出生年月] datetime) 
5. insert [tb] 
6. select 'A','后勤','高中','1986-1-1' union all 
7. select 'B','后勤','初中','1984-3-7' union all 
8. select 'C','管理','本科','1987-2-1' union all 
9. select 'D','操作','专科','1976-2-1' union all 
10. select 'E','操作','专科','1943-2-1'  
11.  
12.  
13. --------------开始查询-------------------------- 
14. declare @sql varchar(8000) 
15. set @sql = 'select 部门,dbo.AgeLevel([出生年月]) as 年龄段' 
16. select @sql = @sql + ' , sum(case 学历 when ''' + 学历 + ''' then 1 else 0 end) [' + 学历 + ']' 
17. from (select distinct 学历 from tb) as a 
18. set @sql = @sql + ' from tb group by 部门,dbo.AgeLevel([出生年月])' 
19. exec(@sql)  
20.  
21. /*  
22. 部门   年龄段        本科          初中          高中          专科 
23. ---- ---------- ----------- ----------- ----------- ----------- 
24. 管理   21－30      1           0           0           0 
25. 后勤   21－30      0           1           1           0 
26. 操作   31－40      0           0           0           1 
27. 操作   50以上       0           0           0           1 
28.  
29. (4 行受影响) 
30. */ 
31.  
32.  
33. drop function AgeLevel  
34. go  
35. --获取年龄段  
36. create function AgeLevel(@birthday datetime)  
37. returns varchar(10)  
38. as  
39. begin  
40. declare  @AgeLevel varchar(10)  
41.  
42. select @AgeLevel=case((datediff(year,@birthday,getdate())-1)/10) when 2 then '21－30' when 3 then '31－40' when 4 then'41－50' else '50以上' end   
43. return @AgeLevel  
44. end  
45. go  
46.  
47. select * ,dbo.AgeLevel([出生年月]) as 年龄段 from tb 
48. /* 
49. 姓名   部门   学历   出生年月                    年龄段 
50. ---- ---- ---- ----------------------- ---------- 
51. A    后勤   高中   1986-01-01 00:00:00.000 21－30 
52. B    后勤   初中   1984-03-07 00:00:00.000 21－30 
53. C    管理   本科   1987-02-01 00:00:00.000 21－30 
54. D    操作   专科   1976-02-01 00:00:00.000 31－40 
55. E    操作   专科   1943-02-01 00:00:00.000 50以上 
56. */ 
57.  
58.  
59.  
60.  
61.  
62. select N'年龄段'=( 
63. case((datediff(year,[出生年月],getdate())-1)/10)   
64. when 2 then '21－30'    
65. when 3 then '31－40'  
66. when 4 then'41－50' 
67. else '50以上' 
68. end),    
69. count(*) as count      
70. from tb    
71. group by ( 
72. case((datediff(year,[出生年月],getdate())-1)/10)    
73. when   2   then   '21－30'    
74. when   3   then   '31－40'    
75. when   4   then'41－50'    
76. else   '50以上'    
77. end   ) 
78. /* 
79. 年龄段    count 
80. ------ ----------- 
81. 21－30  3 
82. 31－40  1 
83. 50以上   1 
84.  
85. (3 行受影响) 
86. */ 
87.  
88.  
89.  
90.  
91.  
92. --以10岁为递增 
93. select  
94. cast(f1*10+1 as varchar(3))+'-'+cast(f1*10+10 as varchar(3)) as 年龄段,f2 as 人数  
95. from  
96. ( 
97. select datediff(d,[出生年月],getdate())/365/10 as f1, 
98. count(*) as f2  
99. from tb  
100. group by datediff(d,[出生年月],getdate())/365/10) a  
101. order by cast(f1*10+1 as varchar(3))+'-'+cast(f1*10+10 as varchar(3))  
102. /* 
103. 年龄段     人数 
104. ------- ----------- 
105. 21-30   3 
106. 31-40   1 
107. 61-70   1 
108.  
109. (3 行受影响) 
110. */ 
111.  
112.  
113.  
114.  
115. SELECT  
116. SUM( 
117. CASE WHEN datediff(year, [出生年月], getdate()) BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS '16-20',  
118. SUM(CASE WHEN datediff(year, [出生年月], getdate()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS '21-30',  
119. SUM(CASE WHEN datediff(year, [出生年月], getdate()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '31-40',  
120. SUM(CASE WHEN datediff(year, [出生年月], getdate()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '41-50', 
121. SUM(CASE WHEN datediff(year, [出生年月], getdate()) BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS '51-60',  
122. SUM(CASE WHEN datediff(year, [出生年月], getdate()) BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '61-70'  
123. FROM tb 
124.  
125. /* 
126. 16-20       21-30       31-40       41-50       51-60       61-70 
127. ----------- ----------- ----------- ----------- ----------- ----------- 
128. 0           3           1           0           0           1 
129.  
130. (1 行受影响) 
131. */