求一SQL 关于从当前日期排序先后排序
比如说我用户表中有字段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)以后的员工的生日先后顺序
[解决办法]
--上面的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()) <=0
then 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)
