select age/10,count(*) from tab group by age/10select * from tb order by case when age <16 then 10000 else age end desc
--1 select sum(case when datediff(year,age,getdate())=10 then 1 else 0 end )[10岁人數], sum(case when datediff(year,age,getdate())=20 then 1 else 0 end )[20岁人數], sum(case when datediff(year,age,getdate())=30 then 1 else 0 end )[30岁人數], sum(case when datediff(year,age,getdate())=40 then 1 else 0 end )[40岁人數] from tab --2 select top 20 * from tab order by age desc, case when datediff(year,age,getdate())<16 then 0 else 1 end
select case when age < 10 then '10岁' when age >=10 and age <20 then '20岁' when age >=20 then '30岁' end as 年龄, count(*) from tab group by case when age < 10 then '10岁' when age >=10 and age <20 then '20岁' when age >=20 then '30岁' end; select * from (select top 20 * from tab order by age) as t order by case when age < 16 then -age else age end
select age , count(1) cnt from tab group by ageselect age , count(1) from ( select case when age < 10 then 'age<10' when age < 20 and age >= 10 then '10=<age<20' ... end age from tab ) t group by ageselect top 20 * from tab order by case when age < 16 then 1 else 2 end
1.select age/10,count(*) from tb group by age/10 2. select top 20 *from tb order by case when age <16 then 10000 else age end desc
create table tab(name nvarchar(10),age int) insert into tab values ('a',10) insert into tab values ('b',20) insert into tab values ('c',15) insert into tab values ('d',70)select ltrim(age/10)+'0~'+ltrim(age/10+1)+'0',count(*) from tab group by age/10 /* --------------------------- ----------- 10~20 2 20~30 1 70~80 1(3 row(s) affected)*/ select * from tab order by case when age <16 then 10000 else age end desc /*name age ---------- ----------- a 10 c 15 d 70 b 20(4 row(s) affected) */
--> 测试数据: @tb declare @tb table (age int,name varchar(2)) insert into @tb select 10,'a' union all select 14,'b' union all select 35,'c' union all select 35,'d' union all select 55,'e' union all select 44,'t' union all select 65,'u' union all select 33,'tt'select case when age between 10 and 20 then '[10-20]' when age between 21 and 30 then '[21-30]' when age between 31 and 40 then '[31-40]' when age between 41 and 50 then '[41-50]' when age >50 then '[50以上]' end as 年龄段, count(*)as 人数 from @tb group by case when age between 10 and 20 then '[10-20]' when age between 21 and 30 then '[21-30]' when age between 31 and 40 then '[31-40]' when age between 41 and 50 then '[41-50]' when age >50 then '[50以上]' end 年龄段 人数 -------- ----------- [10-20] 2 [31-40] 3 [41-50] 1 [50以上] 2(4 行受影响)
select case when age < 10 then '10岁' when age >=10 and age <20 then '20岁' when age >=20 then '30岁' end as 年龄, count(*) from tab group by case when age < 10 then '10岁' when age >=10 and age <20 then '20岁' when age >=20 then '30岁' end; select * from (select top 20 * from tab order by age) as t order by case when age < 16 then -age else age end
age/10,count(*)
from
tab
group by age/10select
*
from
tb
order by case when age <16 then 10000 else age end desc
select
sum(case when datediff(year,age,getdate())=10 then 1 else 0 end )[10岁人數],
sum(case when datediff(year,age,getdate())=20 then 1 else 0 end )[20岁人數],
sum(case when datediff(year,age,getdate())=30 then 1 else 0 end )[30岁人數],
sum(case when datediff(year,age,getdate())=40 then 1 else 0 end )[40岁人數]
from tab
--2
select top 20 *
from tab
order by age desc,
case when datediff(year,age,getdate())<16 then 0 else 1 end
case when age < 10 then '10岁'
when age >=10 and age <20 then '20岁'
when age >=20 then '30岁' end as 年龄,
count(*)
from tab
group by case when age < 10 then '10岁'
when age >=10 and age <20 then '20岁'
when age >=20 then '30岁' end;
select *
from (select top 20 * from tab order by age) as t
order by case when age < 16 then -age else age end
(
select case when age < 10 then 'age<10'
when age < 20 and age >= 10 then '10=<age<20'
...
end age
from tab
) t
group by ageselect top 20 * from tab order by case when age < 16 then 1 else 2 end
2.
select top 20 *from tb order by case when age <16 then 10000 else age end desc
insert into tab values ('a',10)
insert into tab values ('b',20)
insert into tab values ('c',15)
insert into tab values ('d',70)select
ltrim(age/10)+'0~'+ltrim(age/10+1)+'0',count(*)
from
tab
group by age/10
/*
--------------------------- -----------
10~20 2
20~30 1
70~80 1(3 row(s) affected)*/
select
*
from
tab
order by case when age <16 then 10000 else age end desc
/*name age
---------- -----------
a 10
c 15
d 70
b 20(4 row(s) affected)
*/
declare @tb table (age int,name varchar(2))
insert into @tb
select 10,'a' union all
select 14,'b' union all
select 35,'c' union all
select 35,'d' union all
select 55,'e' union all
select 44,'t' union all
select 65,'u' union all
select 33,'tt'select
case when age between 10 and 20 then '[10-20]'
when age between 21 and 30 then '[21-30]'
when age between 31 and 40 then '[31-40]'
when age between 41 and 50 then '[41-50]'
when age >50 then '[50以上]'
end as 年龄段,
count(*)as 人数
from @tb
group by
case when age between 10 and 20 then '[10-20]'
when age between 21 and 30 then '[21-30]'
when age between 31 and 40 then '[31-40]'
when age between 41 and 50 then '[41-50]'
when age >50 then '[50以上]'
end
年龄段 人数
-------- -----------
[10-20] 2
[31-40] 3
[41-50] 1
[50以上] 2(4 行受影响)
case when age < 10 then '10岁'
when age >=10 and age <20 then '20岁'
when age >=20 then '30岁' end as 年龄,
count(*)
from tab
group by case when age < 10 then '10岁'
when age >=10 and age <20 then '20岁'
when age >=20 then '30岁' end;
select *
from (select top 20 * from tab order by age) as t
order by case when age < 16 then -age else age end