sqlserver根据年龄统计各年龄阶段的人数

SELECT  CASE ( ( ( CASE ( SIGN(CONVERT(VARCHAR(100),GETDATE() - DATEADD(year,DATEDIFF(year,birthday,GETDATE()),birthday),112)
                               - 19000101) )
                     WHEN 0 THEN DATEDIFF(year,GETDATE()) + 1
                     WHEN 1 THEN DATEDIFF(year,GETDATE()) + 1
                     WHEN -1 THEN DATEDIFF(year,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,112) - 19000101) )
                        WHEN 0 THEN DATEDIFF(year,GETDATE()) + 1
                        WHEN 1 THEN DATEDIFF(year,GETDATE()) + 1
                        WHEN -1 THEN DATEDIFF(year,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 )

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!