如何简化

SENJORCOU

题目为：按角色和就职年份统计人数,年份按0-3，3-6，6-9这种阶梯分组，最后按角色+阶梯分组排序
表格：

ole	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

我写的sql：
select role,
count(case when years_employed <3 then name else null end)as type1,
count(case when years_employed >=3 and years_employed<6then name else null end)as type2,
count(case when years_employed >=6 and years_employed<9 then name else null end) as type3 from employees
group by role order by role;

感觉太麻烦了，能不能写的更简单些？

amazeyeli

挺好的，一目了然
方法2：用Years_employed除以3，然后取整，再透视
select role,[0],[1],[2],[3]
from (select *,years_employed/3 as type from employees) as t
pivot (count(name) for type in ([0],[1],[2],[3])) as p