关于年龄段的统计问题!

人员信息表:  
   
  EmployeeID(工号)     BornDate(出生日期)     WorkModality(用工形式)     Property(工作性质)  
   
   
  注:用工形式分若干中,假设为A.B.C.D  
   
  每种用工形式都有四种工作性质(W,X,Y,Z)  
   
  想得到每种用工形式的各个工作性质在各个年龄段的人数,平均年龄  
   
  年龄段分18~20,21~25,26~30,31~35,36~40,41~45,46~50,51~55,56以上, 问题点数：30、回复次数：9Top

1 楼wzh1215（懒猫）回复于 2003-12-02 09:58:11 得分 5

18-20：  
  select   workmodality,property,count(*)   as   人数,avg(datadiff(yy,borndate,getdate()))   as   平均年龄   from   表   where   datediff(yy,borndate,getdate())>=18   and   datediff(yy,borndate,getdate())<=20   group   by   workmodality,propertyTop

2 楼pengdali（）回复于 2003-12-02 10:01:06 得分 10

select   WorkModality,Property,    
   
  case    
      when   datediff(year,BornDate,getdate())   between   18   and   20   then   '18~20'  
      when   datediff(year,BornDate,getdate())   between   21   and   25   then   '21~25'  
      when   datediff(year,BornDate,getdate())   between   26   and   30   then   '26~30'  
      when   datediff(year,BornDate,getdate())   between   31   and   35   then   '31~35'  
      when   datediff(year,BornDate,getdate())   between   36   and   40   then   '36~40'  
      when   datediff(year,BornDate,getdate())   between   41   and   45   then   '41~45'  
      when   datediff(year,BornDate,getdate())   between   46   and   50   then   '46~50'  
      when   datediff(year,BornDate,getdate())   between   51   and   55   then   '51~55'  
      when   datediff(year,BornDate,getdate())>=56   then   '56以上'  
  end   年龄段,  
   
  count(*)   人数,  
  avg(datediff(year,BornDate,getdate()))   平均年龄  
   
  from   人员信息表   group   by   WorkModality,Property,  
  case    
      when   datediff(year,BornDate,getdate())   between   18   and   20   then   '18~20'  
      when   datediff(year,BornDate,getdate())   between   21   and   25   then   '21~25'  
      when   datediff(year,BornDate,getdate())   between   26   and   30   then   '26~30'  
      when   datediff(year,BornDate,getdate())   between   31   and   35   then   '31~35'  
      when   datediff(year,BornDate,getdate())   between   36   and   40   then   '36~40'  
      when   datediff(year,BornDate,getdate())   between   41   and   45   then   '41~45'  
      when   datediff(year,BornDate,getdate())   between   46   and   50   then   '46~50'  
      when   datediff(year,BornDate,getdate())   between   51   and   55   then   '51~55'  
      when   datediff(year,BornDate,getdate())>=56   then   '56以上'  
  end   年龄段Top

3 楼pengdali（）回复于 2003-12-02 10:01:30 得分 0

select   WorkModality,Property,    
   
  case    
      when   datediff(year,BornDate,getdate())   between   18   and   20   then   '18~20'  
      when   datediff(year,BornDate,getdate())   between   21   and   25   then   '21~25'  
      when   datediff(year,BornDate,getdate())   between   26   and   30   then   '26~30'  
      when   datediff(year,BornDate,getdate())   between   31   and   35   then   '31~35'  
      when   datediff(year,BornDate,getdate())   between   36   and   40   then   '36~40'  
      when   datediff(year,BornDate,getdate())   between   41   and   45   then   '41~45'  
      when   datediff(year,BornDate,getdate())   between   46   and   50   then   '46~50'  
      when   datediff(year,BornDate,getdate())   between   51   and   55   then   '51~55'  
      when   datediff(year,BornDate,getdate())>=56   then   '56以上'  
  end   年龄段,  
   
  count(*)   人数,  
  avg(datediff(year,BornDate,getdate()))   平均年龄  
   
  from   人员信息表   group   by   WorkModality,Property,  
  case    
      when   datediff(year,BornDate,getdate())   between   18   and   20   then   '18~20'  
      when   datediff(year,BornDate,getdate())   between   21   and   25   then   '21~25'  
      when   datediff(year,BornDate,getdate())   between   26   and   30   then   '26~30'  
      when   datediff(year,BornDate,getdate())   between   31   and   35   then   '31~35'  
      when   datediff(year,BornDate,getdate())   between   36   and   40   then   '36~40'  
      when   datediff(year,BornDate,getdate())   between   41   and   45   then   '41~45'  
      when   datediff(year,BornDate,getdate())   between   46   and   50   then   '46~50'  
      when   datediff(year,BornDate,getdate())   between   51   and   55   then   '51~55'  
      when   datediff(year,BornDate,getdate())>=56   then   '56以上'  
  endTop

4 楼realgz（realgz）回复于 2003-12-02 10:02:45 得分 0

group   by   case   ()Top

5 楼loulanlouzhu（桃花潭水深千尺，不及阿勇念你情）回复于 2003-12-02 10:18:48 得分 0

18~20     21~25     26~30   31~35     36~40     41~45     46~50     51~55     56以上  
   
  我想把每个年龄段的人数作为列来显示!!可以在sql   中完成吗@?  
   
   
  ===弯弯的月亮小小的船，小小的船，两头尖，我在小小的船里坐，只看见闪闪  
  的星星蓝蓝的天．  
   
  ===本贴子以“现状”提供且没有任何担保，同时也没有授予任何权利    
  Top

6 楼realgz（realgz）回复于 2003-12-02 10:23:53 得分 0

那自然可以   select   sum(case())就okTop

7 楼victorycyz（--）回复于 2003-12-02 10:32:00 得分 0

问一下高手们，这里的case语句有没有办法写成类似下面这种形式：  
  case    
        datediff(year,BornDate,getdate())  
        when     between   18   and   20   then   '18~20'  
        when     between   21   and   25   then   '21~25'  
        ...  
  end   年龄段  
   
  ---------------------------------------------------  
  原case语句:  
  case    
      when   datediff(year,BornDate,getdate())   between   18   and   20   then   '18~20'  
      when   datediff(year,BornDate,getdate())   between   21   and   25   then   '21~25'  
      when   datediff(year,BornDate,getdate())   between   26   and   30   then   '26~30'  
      when   datediff(year,BornDate,getdate())   between   31   and   35   then   '31~35'  
      when   datediff(year,BornDate,getdate())   between   36   and   40   then   '36~40'  
      when   datediff(year,BornDate,getdate())   between   41   and   45   then   '41~45'  
      when   datediff(year,BornDate,getdate())   between   46   and   50   then   '46~50'  
      when   datediff(year,BornDate,getdate())   between   51   and   55   then   '51~55'  
      when   datediff(year,BornDate,getdate())>=56   then   '56以上'  
  end   年龄段,  
  Top

8 楼loulanlouzhu（桃花潭水深千尺，不及阿勇念你情）回复于 2003-12-02 10:36:09 得分 0

To:victorycyz(中海，干活去，别在CSDN玩耍！)    
   
  我试了下,好像不可以!  
   
   
  ===弯弯的月亮小小的船，小小的船，两头尖，我在小小的船里坐，只看见闪闪  
  的星星蓝蓝的天．  
   
  ===本贴子以“现状”提供且没有任何担保，同时也没有授予任何权利    
  Top

9 楼zjcxc（邹建）回复于 2003-12-02 11:01:06 得分 15

select   Property  
  ,[18~20]=sum(case   when   old   between   18   and   20   then   1   else   0   end)  
  ,[21~25]=sum(case   when   old   between   21   and   25   then   1   else   0   end)  
  ,[26~30]=sum(case   when   old   between   26   and   30   then   1   else   0   end)  
  ,[31~35]=sum(case   when   old   between   31   and   35   then   1   else   0   end)  
  ,[36~40]=sum(case   when   old   between   36   and   40   then   1   else   0   end)  
  ,[41~45]=sum(case   when   old   between   41   and   45   then   1   else   0   end)  
  ,[46~50]=sum(case   when   old   between   46   and   50   then   1   else   0   end)  
  ,[51~55]=sum(case   when   old   between   51   and   55   then   1   else   0   end)  
  ,[56以上]=sum(case   when   old>55   then   1   else   0   end)  
  from(  
  select   Property,old=datediff(year,BornDate,getdate())  
  from   人员信息表  
  )   a  
  group   by   PropertyTop

相关问题

• borndate
• getdate
• 年龄段
• workmodality
• when datediff
• year
• 平均年龄
• 用工形式
• datediff
• 人数

• 帖主：loulanlouzhu
• wzh1215
• pengdali
• zjcxc

• SQL Server类图书