搜索学习记录：/topics/391976117
create table #t(recordkey varchar(10),age int)insert into #tselect '0001_1',8 union allselect '0001_2',8 union allselect '0001_3',8 union allselect '0004_1',9 union allselect '0002_1',13 union allselect '0003_1',22select a.ageRange,qty=sum(isnull(b.qty,0))from(select ageRange=rtrim(number)+'-'+rtrim(number+10),agebegin=number,ageEnd=number+10from master.dbo.spt_valueswhere type='P' and number between 0 and 100and number%10=0) aleft join(select age,'qty'=count(distinct left(recordkey,4))from #tgroup by age) b on b.age between a.agebegin and a.ageEndgroup by a.ageRangeorder by min(a.agebegin)/*ageRange qty------------------------- -----------0-10 210-20 120-30 130-40 040-50 050-60 060-70 070-80 080-90 090-1000100-110 0(11 row(s) affected)*/
增加区间分级的练习：
create table #t(recordkey varchar(10),age int,age1 int,age2 int,age3 int)insert into #tselect '0001_1',8,0,10,20 union allselect '0001_2',8,0,10,20 union allselect '0001_3',8,0,10,20 union allselect '0004_1',9,0,10,20 union allselect '0002_1',13,0,10,20 union allselect '0003_1',22,0,10,20SELECT *, (casewhen age BETWEEN age1 and age2 then '1'when age BETWEEN age2 and age3 then '2'when age > age3 then '3'else '00'end) as 分级 from #tselect a.ageRange,qty=sum(isnull(b.qty,0))from(select ageRange=rtrim(number)+'-'+rtrim(number+10),agebegin=number,ageEnd=number+10from master.dbo.spt_valueswhere type='P' and number between 0 and 100and number%10=0) aleft join(select age,'qty'=count(distinct left(recordkey,4))from #tgroup by age) b on b.age between a.agebegin and a.ageEndgroup by a.ageRangeorder by min(a.agebegin)/*ageRange qty- -0-10 210-20 120-30 130-40 040-50 050-60 060-70 070-80 080-90 090-1000100-110 0(11 row(s) affected)*/
