SQL统计-关于人口年龄

转载

mob604756e62e3d 2007-07-23 16:32:00

文章标签 ide 文章分类 代码人生

相关数据表：

人口表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_People](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [sex] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [birthday] [datetime] NOT NULL,
CONSTRAINT [PK_T_People] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

要求统计如下：

年龄结构

年龄\性别	男	女	总计
1-10岁	3	8	11
11-20岁	2	2	4
21-30岁	5	5	10
31-40岁	2	4	6
41-50岁	2	1	3
51-60岁	3	1	4
71-80岁	10	6	16
81-90岁	1	1	2
91-100岁	3	5	8

select age_range as 年龄段 ,sum(count) as 总数,
(
   select count from
   (
     select  sex,age_range,count(*) as count from
     (
       select sex,(((cast(getdate() as int)-cast(birthday as int))/365-1)/10) as age_range
       from T_People
     ) tb_1
     where age_range>=0   group by age_range,sex
   ) tb_2
   where sex='男' and age_range=tb_3.age_range
) as 男,
(
       select count from
      (
        select  sex,age_range,count(*) as count from
        (
          select sex,(((cast(getdate() as int)-cast(birthday as int))/365-1)/10) as age_range
          from T_People
        ) tb_1
        where age_range>=0   group by age_range,sex
      ) tb_2
      where sex='女' and age_range=tb_3.age_range
  ) as 女
  from
  (
    select  sex,age_range,count(*) as count from
    (
      select sex,(((cast(getdate() as int)-cast(birthday as int))/365-1)/10) as age_range
      from T_People
    ) tb_1
    where age_range>=0   group by age_range,sex
  ) tb_3
  group by age_range order by age_range