高分求一查询语句

Question

数据表中包含【出生日期】和【性别】两个字段。其中【性别】中1=男，2=女， 分年龄段统计出男女的人数。 假设数据表名称为 tb, 测试数据如下：出生日期        性别
============================================
1976-07-04       1
1953-12-04       2
2009-10-02       1
.......要求SELECT 查询输出为
年龄段     男         女           合计
======================================================
<15岁     XXX         XXX1         XXX + XXX1 （男性人数 + 女性人数）
15-40岁   XXX         XXX1         XXX + XXX1
40-60岁   XXX         XXX1         XXX + XXX1
>60岁     XXX         XXX1         XXX + XXX1
总计   所有男性累加   女性人数累加  合计累加   要求只能用一条SELECT 语句统计出所需数据。

Answer 1

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (出生日期 datetime,性别 int)
insert into [tb]
select '1976-07-04',1 union all
select '1953-12-04',2 union all
select '2009-10-02',1
select 年龄段='<15岁',男=sum(case when datediff(yy,出生日期,getdate())<15 and 性别=1 then 1 else 0 end),女=sum(case when datediff(yy,出生日期,getdate())<15 and 性别=2 then 1 else 0 end),合计=sum(case when datediff(yy,出生日期,getdate())<15 then 1 else 0 end) from [tb]
union all
select 年龄段='15-40岁',sum(case when datediff(yy,出生日期,getdate()) between 15 and 40 and 性别=1 then 1 else 0 end),sum(case when datediff(yy,出生日期,getdate()) between 15 and 40 and 性别=2 then 1 else 0 end),sum(case when datediff(yy,出生日期,getdate()) between 15 and 40 then 1 else 0 end) from [tb]
union all
select 年龄段='41-60岁',sum(case when datediff(yy,出生日期,getdate()) between 41 and 60 and 性别=1 then 1 else 0 end),sum(case when datediff(yy,出生日期,getdate()) between 41 and 60 and 性别=2 then 1 else 0 end),sum(case when datediff(yy,出生日期,getdate()) between 41 and 60 then 1 else 0 end) from [tb]
union all
select 年龄段='>60岁',sum(case when datediff(yy,出生日期,getdate())>60 and 性别=1 then 1 else 0 end),sum(case when datediff(yy,出生日期,getdate())>60 and 性别=2 then 1 else 0 end),sum(case when datediff(yy,出生日期,getdate())>60 then 1 else 0 end) from [tb]
union all
select '总计',sum(case when 性别=1 then 1 else 0 end),sum(case when 性别=2 then 1 else 0 end),count(*) from [tb]--结果：
年龄段     男           女           合计
------- ----------- ----------- -----------
<15岁    1           0           1
15-40岁  1           0           1
41-60岁  0           1           1
>60岁    0           0           0
总计      2           1           3

Answer 2

向3楼学习，看来when case 挺有用的

Answer 3

select 年龄段,
sum(case when 性别 =1 then 0 else 1 end) '男',
sum(case when 性别 =1 then 0 else 1 end) '女',
sum(1) '合计'
from
(
select 年龄段 = case when YMD <15 then '<15岁' 
when YMD between 15 and 40 then  '15-40岁'
when YMD between 40 and 60 then  '40-60岁'
else then '>60岁' end,性别
from(
select datediff(yy,出生日期,getdate()) YMD,性别
from tab
)T
)T_tab

Answer 4

错了，
select 年龄段, 
sum('男') '男' ,
sum('女') '女',
sum('男') + sum('女') '合计' 
from ( 
select 年龄段 = 
case when YMD <15 then '<15岁' 
when YMD between 15 and 40 then '15-40岁' 
when YMD between 40 and 60 then '40-60岁' 
else then '>60岁' 
end,
'男' =sum(case when 性别 =1 then 0 else 1 end) , 
'女' =sum(case when 性别 =1 then 0 else 1 end) 
from
( select datediff(yy,出生日期,getdate()) YMD,性别 from tab )T 
)T_tab
group by 年龄段

Answer 5

上面不错
只是年龄计算再判断一下生日问题，就完整了--测试数据
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
create table tb (出生日期 datetime,性别 int)
insert into tb
select '1976-07-04',1 union all
select '1953-12-04',2 union all
select '2009-10-02',1
go
--查询
select 
case when grouping(年龄段)=1 then '合计'  else 年龄段 end 年龄段,
sum(case 性别 when 1 then 1 else 0 end )as 男,
sum(case 性别 when 2 then 1 else 0 end )as 女,
count(b.性别) as 合计
from(
select 0 as b,14 as e,'<15岁' as 年龄段 union all
select 15 as b,39 as e,'15-40岁' as 年龄段 union all
select 40 as b,59 as e,'40-60岁' as 年龄段 union all
select 60 as b,999 as e,'>60岁' as 年龄段
) a left join 
(
select
case when dateadd(year,datediff(year,出生日期,getdate()),出生日期)<getdate()
then datediff(year,出生日期,getdate())-1 
else datediff(year,出生日期,getdate())
end as 年龄,
性别 
from tb)b on b.年龄 between a.b and a.e
group by 年龄段
with rollup
--结果
/*
年龄段     男           女           合计
------- ----------- ----------- -----------
<15岁    1           0           1
>60岁    0           0           0
15-40岁  1           0           1
40-60岁  0           1           1
合计      2           1           3
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)*/

Answer 6

IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
GO
CREATE TABLE tb ( 出生日期 DATETIME, 性别 INT )
INSERT  INTO tb
SELECT  '1976-07-04' , 1 UNION ALL
SELECT  '1953-12-04' , 2 UNION ALL
SELECT  '2009-10-02' , 1
GOSELECT 年龄段 = ISNULL(年龄段,'合计')
      ,男 = COUNT(CASE WHEN 性别 = 1 THEN 性别 END)
      ,女 = COUNT(CASE WHEN 性别 = 2 THEN 性别 END)
      ,合计 = COUNT(*) 
FROM 
(
    SELECT CASE WHEN DATEDIFF(yy,出生日期,GETDATE()) < 15 THEN '<15岁'
                WHEN DATEDIFF(yy,出生日期,GETDATE()) BETWEEN 15 AND 40 THEN '15-40岁'
                WHEN DATEDIFF(yy,出生日期,GETDATE()) BETWEEN 41 AND 60 THEN '40-60岁'
                WHEN DATEDIFF(yy,出生日期,GETDATE()) > 60 THEN '>60岁' END AS 年龄段
        ,性别
    FROM tb) AS A
GROUP BY 年龄段
WITH ROLLUP