根据年龄统计各年龄阶段的人数代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】根据年龄统计各年龄阶段的人数

作者:Dezai.CN / 发布于2013/1/5/ 666

SELECT  CASE ( ( ( CASE ( SIGN(CONVERT(VARCHAR(100), GETDATE() - DATEADD(year,
                                                              DATEDIFF(year,
                                                              birthday,
                                                              GETDATE()),
                                                              birthday), 112)
                               - 19000101) )
                     WHEN 0 THEN DATEDIFF(year, birthday, GETDATE()) + 1
                     WHEN 1 THEN DATEDIFF(year, birthday, GETDATE()) + 1
                     WHEN -1 THEN DATEDIFF(year, birthday, GETDATE())
                   END ) - 1 ) / 5 + 1 )
          WHEN 1 THEN '<20'
          WHEN 2 THEN '<20'
          WHEN 3 THEN '<20'
          WHEN 4 THEN '<20'
          WHEN 5 THEN '20~25'
          WHEN 6 THEN '25~30'
          WHEN 7 THEN '31~35'
          WHEN 8 THEN '36~40'
          WHEN 9 THEN '41~45'
          WHEN 10 THEN '46~50'
          WHEN 11 THEN '51~55'
          WHEN 12 THEN '56~60'
          ELSE '>=60'
        END 'age distribution ' ,
        COUNT(*) 'total'
FROM    dbo.person p  
GROUP BY ( CASE ( ( ( CASE ( SIGN(CONVERT(VARCHAR(100), GETDATE()
                                  - DATEADD(year,
                                            DATEDIFF(year, birthday, GETDATE()),
                                            birthday), 112) - 19000101) )
                        WHEN 0 THEN DATEDIFF(year, birthday, GETDATE()) + 1
                        WHEN 1 THEN DATEDIFF(year, birthday, GETDATE()) + 1
                        WHEN -1 THEN DATEDIFF(year, birthday, GETDATE())
                      END ) - 1 ) / 5 + 1 )
             WHEN 1 THEN '<20'
             WHEN 2 THEN '<20'
             WHEN 3 THEN '<20'
             WHEN 4 THEN '<20'
             WHEN 5 THEN '20~25'
             WHEN 6 THEN '25~30'
             WHEN 7 THEN '31~35'
             WHEN 8 THEN '36~40'
             WHEN 9 THEN '41~45'
             WHEN 10 THEN '46~50'
             WHEN 11 THEN '51~55'
             WHEN 12 THEN '56~60'
             ELSE '>=60'
           END )

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　各年龄阶段　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)