select type , count(*) from ( select * , case when datediff(year,birthday,getdate()) <= 35 then '35岁以下' when datediff(year,birthday,getdate()) >= 36 and datediff(year,birthday,getdate()) <= 45 then '36-45岁' when datediff(year,birthday,getdate()) >= 46 and datediff(year,birthday,getdate()) <= 55 then '46-55岁' when datediff(year,birthday,getdate()) >= 56 and datediff(year,birthday,getdate()) <= 65 then '56-65岁' when datediff(year,birthday,getdate()) > 65 and then '65岁以上' end type from tb ) t group by type
select sum(case when age <35 then 1 else 0 end) [35岁以下] table where 条件... union all select sum(case when age between 36 and 45 then 1 else 0 end)[36-45岁] from table where 条件... union all select sum(case when age between 46 and 55 then 1 else 0 end)[46-55岁] from table where 条件... union all select sum(case when age between 56 and 65 then 1 else 0 end)[55-65岁] from table where 条件.. union all select sum(case when age >65 then 1 else 0 end) [65岁以上] from table where 条件..
比较赞成dagugui的写法,关键是用到DATEDIFF()函数
declare @tb table (name varchar(8000),age int) insert @tb select '张三',5 insert @tb select '李四',15 insert @tb select '王五',30 insert @tb select '赵六',21 insert @tb select '七驴',62 insert @tb select '八马',50 insert @tb select '张飞',48 insert @tb select '关羽',52 insert @tb select '赵云',81 select *,(case when age between 0 and 25 then '0-25岁' when age between 26 and 50 then '26-50岁' when age between 51 and 75 then '51-75岁' else '高龄' end) from @tb group by (case when age between 0 and 25 then '0-25岁' when age between 26 and 50 then '26-50岁' when age between 51 and 75 then '51-75岁' else '高龄' end) ,name,age李四 15 0-25岁 张三 5 0-25岁 赵六 21 0-25岁 八马 50 26-50岁 王五 30 26-50岁 张飞 48 26-50岁 关羽 52 51-75岁 七驴 62 51-75岁 赵云 81 高龄
select type , count(*) from ( select * , case when datediff(year,birthday,getdate()) <= 35 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '35岁以下' when datediff(year,birthday,getdate()) between 36 and 45 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '36-45岁' when datediff(year,birthday,getdate()) between 46 and 55 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '46-55岁' when datediff(year,birthday,getdate()) between 56 and 65 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '56-65岁' when datediff(year,birthday,getdate()) > 65 and datepart(month,birthday>datepart(month,getdate()) and datepart(day,birthday)>datepart(day,getdate()) then '65岁以上' end type from tb ) t group by type
datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) 为什么要加这句呢?
select ages , count(*) from ( select * , ages=case (datediff(y, birthday, dateadd(d, -day(birthday), dateadd(m, -month(birthday), getdate())))-36)/10 when 0 then '36-45岁' when 1 then '46-55岁' when 2 then '56-65岁' when 3 then '65岁以上' --'66-75岁' when 4 then '65岁以上' --'76-85岁' when 5 then '65岁以上' --'86-95岁' when 6 then '65岁以上' --'96-105岁' else '35岁以下' end from tb ) t group by ages
datepart(month,birthday) <=datepart(month,getdate()) and datepart(day,birthday) <=datepart(day,getdate()) 为什么要加这句呢? -------------------1980-08-22如果只算年份的话,只有27岁,如果要算得精确的话,就是28岁了.
create table tb(name varchar(32), birthday datetime)insert tb select 'dobear','1983-09-22' union all select 'wugui','1971-2-2' union all select 'dawugui','1971-12-2' union all select 'god','1922-1-1' union all select 'dog','2007-1-1' union all select 'ilsa','1945-1-1'select * , ages=case (datediff(year, birthday, dateadd(d, -day(birthday), dateadd(m, -month(birthday), getdate())))+34)/10 when 7 then '36-45岁' when 8 then '46-55岁' when 9 then '56-65岁' when 10 then '65岁以上' --'66-75岁' when 11 then '65岁以上' --'76-85岁' when 12 then '65岁以上' --'86-95岁' when 13 then '65岁以上' --'96-105岁' else '35岁以下' end from tbdrop table tb/* name birthday ages -------------------------------- ----------------------- -------- dobear 1983-09-22 00:00:00.000 35岁以下 wugui 1971-02-02 00:00:00.000 36-45岁 dawugui 1971-12-02 00:00:00.000 35岁以下 god 1922-01-01 00:00:00.000 65岁以上 dog 2007-01-01 00:00:00.000 35岁以下 ilsa 1945-01-01 00:00:00.000 56-65岁(6 row(s) affected) */
select type , count(*) from ( select * , case when datediff(year,birthday,getdate()) <= 35 then '35岁以下' when datediff(year,birthday,getdate()) >= 36 and datediff(year,birthday,getdate()) <= 45 then '36-45岁' when datediff(year,birthday,getdate()) >= 46 and datediff(year,birthday,getdate()) <= 55 then '46-55岁' when datediff(year,birthday,getdate()) >= 56 and datediff(year,birthday,getdate()) <= 65 then '56-65岁' when datediff(year,birthday,getdate()) > 65 and then '65岁以上' end type from tb ) t group by type
(
select * , case when datediff(year,birthday,getdate()) <= 35 then '35岁以下'
when datediff(year,birthday,getdate()) >= 36 and datediff(year,birthday,getdate()) <= 45 then '36-45岁'
when datediff(year,birthday,getdate()) >= 46 and datediff(year,birthday,getdate()) <= 55 then '46-55岁'
when datediff(year,birthday,getdate()) >= 56 and datediff(year,birthday,getdate()) <= 65 then '56-65岁'
when datediff(year,birthday,getdate()) > 65 and then '65岁以上'
end type
from tb
) t
group by type
union all
select sum(case when age between 36 and 45 then 1 else 0 end)[36-45岁] from table where 条件...
union all
select sum(case when age between 46 and 55 then 1 else 0 end)[46-55岁] from table where 条件...
union all
select sum(case when age between 56 and 65 then 1 else 0 end)[55-65岁] from table where 条件..
union all
select sum(case when age >65 then 1 else 0 end) [65岁以上] from table where 条件..
declare @tb table (name varchar(8000),age int)
insert @tb select '张三',5
insert @tb select '李四',15
insert @tb select '王五',30
insert @tb select '赵六',21
insert @tb select '七驴',62
insert @tb select '八马',50
insert @tb select '张飞',48
insert @tb select '关羽',52
insert @tb select '赵云',81 select *,(case when age between 0 and 25 then '0-25岁' when age between 26 and 50 then '26-50岁'
when age between 51 and 75 then '51-75岁' else '高龄' end)
from @tb
group by (case when age between 0 and 25 then '0-25岁' when age between 26 and 50 then '26-50岁'
when age between 51 and 75 then '51-75岁' else '高龄' end)
,name,age李四 15 0-25岁
张三 5 0-25岁
赵六 21 0-25岁
八马 50 26-50岁
王五 30 26-50岁
张飞 48 26-50岁
关羽 52 51-75岁
七驴 62 51-75岁
赵云 81 高龄
select type , count(*) from
(
select * , case when datediff(year,birthday,getdate()) <= 35 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '35岁以下'
when datediff(year,birthday,getdate()) between 36 and 45 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '36-45岁'
when datediff(year,birthday,getdate()) between 46 and 55 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '46-55岁'
when datediff(year,birthday,getdate()) between 56 and 65 and datepart(month,birthday)<=datepart(month,getdate()) and datepart(day,birthday)<=datepart(day,getdate()) then '56-65岁'
when datediff(year,birthday,getdate()) > 65 and datepart(month,birthday>datepart(month,getdate()) and datepart(day,birthday)>datepart(day,getdate()) then '65岁以上'
end type
from tb
) t
group by type
(
select * , ages=case (datediff(y, birthday, dateadd(d, -day(birthday), dateadd(m, -month(birthday), getdate())))-36)/10
when 0 then '36-45岁'
when 1 then '46-55岁'
when 2 then '56-65岁'
when 3 then '65岁以上' --'66-75岁'
when 4 then '65岁以上' --'76-85岁'
when 5 then '65岁以上' --'86-95岁'
when 6 then '65岁以上' --'96-105岁'
else '35岁以下' end
from tb
) t
group by ages
datepart(month,birthday) <=datepart(month,getdate()) and datepart(day,birthday) <=datepart(day,getdate()) 为什么要加这句呢?
-------------------1980-08-22如果只算年份的话,只有27岁,如果要算得精确的话,就是28岁了.
union all select 'wugui','1971-2-2'
union all select 'dawugui','1971-12-2'
union all select 'god','1922-1-1'
union all select 'dog','2007-1-1'
union all select 'ilsa','1945-1-1'select * , ages=case (datediff(year, birthday, dateadd(d, -day(birthday), dateadd(m, -month(birthday), getdate())))+34)/10
when 7 then '36-45岁'
when 8 then '46-55岁'
when 9 then '56-65岁'
when 10 then '65岁以上' --'66-75岁'
when 11 then '65岁以上' --'76-85岁'
when 12 then '65岁以上' --'86-95岁'
when 13 then '65岁以上' --'96-105岁'
else '35岁以下' end
from tbdrop table tb/*
name birthday ages
-------------------------------- ----------------------- --------
dobear 1983-09-22 00:00:00.000 35岁以下
wugui 1971-02-02 00:00:00.000 36-45岁
dawugui 1971-12-02 00:00:00.000 35岁以下
god 1922-01-01 00:00:00.000 65岁以上
dog 2007-01-01 00:00:00.000 35岁以下
ilsa 1945-01-01 00:00:00.000 56-65岁(6 row(s) affected)
*/
(
select * , case when datediff(year,birthday,getdate()) <= 35 then '35岁以下'
when datediff(year,birthday,getdate()) >= 36 and datediff(year,birthday,getdate()) <= 45 then '36-45岁'
when datediff(year,birthday,getdate()) >= 46 and datediff(year,birthday,getdate()) <= 55 then '46-55岁'
when datediff(year,birthday,getdate()) >= 56 and datediff(year,birthday,getdate()) <= 65 then '56-65岁'
when datediff(year,birthday,getdate()) > 65 and then '65岁以上'
end type
from tb
) t
group by type