菜鸟请教一个简单的查询问题

Question

一个查询问题：表patient和表illtype，paitient包括patientid,birthday(datetime类型),illtypeid三个字段，illtype包括illtypeid和illtypename两个字段。现在要查询年龄在30以下，31-40之间，41-50,51-60之间,60以上的病人的信息

Answer 1

select patientid,
(case when datediff(yy,birthday,getdate())<=30 then '30以下'
when datediff(yy,birthday,getdate())<=40 then '31-40'
when datediff(yy,birthday,getdate())<=50 then '41-50'
when datediff(yy,birthday,getdate())<=60 then '51-60'
else '61以上'
end)年龄
from paitient
order by 2

Answer 2

create table patient
(patientid int ,
birthday datetime,
illtypeid int)create table illtype
(
illtypeid int,
illtypename nvarchar(50)
)insert into patient
select 1,'2000-01-01',1 union all
select 2,'2002-01-01',2 union all
select 3,'1982-01-01',2 union all
select 4,'1975-01-01',2 union all
select 5,'1956-01-01',3insert into  illtype
select 1,N'aaaa' union all
select 2,N'bbbb' union all
select 3,N'cccc'SELECT a.patientid,
       CASE 
            WHEN DATEDIFF(YEAR, a.birthday, GETDATE()) <= 30 THEN '30以下'
            WHEN DATEDIFF(YEAR, a.birthday, GETDATE()) <= 40 THEN '31-40'
            WHEN DATEDIFF(YEAR, a.birthday, GETDATE()) <= 50 THEN '41-50'
            WHEN DATEDIFF(YEAR, a.birthday, GETDATE()) <= 60 THEN '51-60'
            ELSE '61以上'
       END 年龄,
       a.illtypeid,
       b.illtypename
FROM   patient a
       LEFT JOIN illtype b
            ON  b.illtypeid = a.illtypeid

Answer 3

select patientid,
(case when datediff(yy,birthday,getdate())<=30 then '30以下'
    when datediff(yy,birthday,getdate()) between 31 and 40 then '31-40'
    when datediff(yy,birthday,getdate()) between 41 and 50  then '41-50'
    when datediff(yy,birthday,getdate()) between 51 and 60 then '51-60'
    else '61以上'
end)年龄
from paitient
order by 2

Answer 4

老大们都不用between ...and 的么？

Answer 5

select ,年龄段=case when datediff(yy,birthday,getdate()) between 31 and 40 then '31-40'
                    when datediff(yy,birthday,getdate()) between 41 and 50 then '41-50'
                    when datediff(yy,birthday,getdate()) between 51 and 60 then '51-60'
                    when datediff(yy,birthday,getdate()) > 60 then '60以上'
               end,
patientid,
birthday,
illtypename 
from paitient a inner join illtype b on a.illtypeid=b.illtypeid 
where datediff(yy,birthday,getdate())>30 order by 年龄段