比如说我用户表中有字段id,birth(生日)
id birth
例如有记录 1 1981-01-15
2 1975-02-24
3 1965-01-19我现在想通过一个sql语句,得到的记录为
3 1965-01-19
2 1975-02-24
1 1981-01-15
就是查出从当前日期(2007-01-17)以后的员工的生日先后顺序
id birth
例如有记录 1 1981-01-15
2 1975-02-24
3 1965-01-19我现在想通过一个sql语句,得到的记录为
3 1965-01-19
2 1975-02-24
1 1981-01-15
就是查出从当前日期(2007-01-17)以后的员工的生日先后顺序
---------
可能吗?
上面回答的都不对,select id,birth from table1 where birth>getdate() order by birth asc这样的语句怎么可能对呢?想想就应该知道,
select * from userlist where cast('1981-'+cast(datepart(mm,birth) as varchar)+'-'+cast(datepart(dd,birth) as varchar) as smalldatetime)>=cast('1981-'+cast(datepart(mm,getdate()) as varchar)+'-'+cast(datepart(dd,getdate()) as varchar) as smalldatetime) order by cast('1981-'+cast(datepart(mm,birth) as varchar)+'-'+cast(datepart(dd,birth) as varchar) as smalldatetime) asc不过这样只能查出今天以后的员工的生日记录,如果员工的生日已经过去的话,就不能查出来,
select * from #temp
order by datediff(dd,'2000'+year(birth)+month(birth),getdate()) desc
(id int,
birth datetime
)
insert into #temp
select '1','1981-01-15' union all
select '2','1975-02-24' union all
select '3','1965-01-19'
select * from #temp
查询语句:(其中2000换成任何年份都可以,主要思路是在同一年份下比较月日之间的天数差别大小来做排序)
select * from #temp
order by datediff(dd,'2000'+year(birth)+month(birth),getdate()) desc
---------------------
id birth3 1965-01-19 00:00:00.000
2 1975-02-24 00:00:00.000
1 1981-01-15 00:00:00.000
To:rookie_one按你这种方法的话,应该也不对,你没有考虑到时间间隔相等的时候,比如生日是01-16和01-18,今天是01-17,两个生日的时间间隔一样,你说应该让谁在先呢?
-----------------
你尝试一下就知道了虽然时间间隔一样,但是一正一负,照样能得到正确顺序~
(id int,
birth datetime
)
insert into #temp
select '1','1981-01-16' union all select '2','1975-02-24' union all select '3','1965-01-18'
select * from #temp select * from #temp
order by datediff(dd,'2000'+year(birth)+month(birth),getdate()) desc------3 1965-01-18 00:00:00.000
2 1975-02-24 00:00:00.000
1 1981-01-16 00:00:00.000
id birth
1 1978-01-17
2 1974-01-04
3 1964-06-02
4 1965-01-01
5 1965-01-12我的SQL语句是
select * from userlist order by datediff(dd,'2000'+month(birth)+day(birth),'2000'+month(getdate())+day(getdate())) desc出来的结果是
4 1965-01-01 //差值是16
2 1974-01-04 //差值是13
3 1964-06-02 //差值是-136
5 1965-01-12 //差值是5
1 1978-01-17 //差值是0是怎么回事啊,我要的结果应该是 1,3,4,2,5
insert T select 1, '1981-01-15'
union all select 2, '1975-02-24'
union all select 3, '1965-01-19'
union all select 4, '1981-01-13'select *
from T
order by
case when datediff(day, dateadd(year, datediff(year, birth, getdate()), birth), getdate())<0then abs( datediff(day, dateadd(year, datediff(year, birth, getdate()), birth), getdate()) )else 365-datediff(day, dateadd(year, datediff(year, birth, getdate()), birth), getdate()) end --result
id birth
----------- ------------------------------------------------------
3 1965-01-19 00:00:00.000
2 1975-02-24 00:00:00.000
4 1981-01-13 00:00:00.000
1 1981-01-15 00:00:00.000(4 row(s) affected)
当差值是都是小于0的时候,应该是以desc排列
当差值是都是大于0的时候,应该是以desc排列
当差值是都是等于0的时候,应该是排在最前面可是当既有大于0,又有小于0的时候,该如何写这个SQL 语句呢?
create table T(id int, birth datetime)
insert T select 1, '1978-01-17'
union all select 2, '1974-01-04'
union all select 3, '1964-06-02'
union all select 4, '1965-01-01'
union all select 5, '1965-01-12'select *
from T
order by
case when datediff(day, dateadd(year, datediff(year, birth, getdate()), birth), getdate())<=0then abs( datediff(day, dateadd(year, datediff(year, birth, getdate()), birth), getdate()) )else 365-datediff(day, dateadd(year, datediff(year, birth, getdate()), birth), getdate()) end --result
id birth
----------- ------------------------------------------------------
1 1978-01-17 00:00:00.000
3 1964-06-02 00:00:00.000
4 1965-01-01 00:00:00.000
2 1974-01-04 00:00:00.000
5 1965-01-12 00:00:00.000(5 row(s) affected)