如何将最后结果按接下去最近生日的顺序排列,

[zitiger]

如何将最后结果按接下去最近生日的顺序排列,


表Member下有birthday字段,如今天是2003-12-19,接下去就应该是
****-12-20
.
.
.
.
.
.
****-1-1
.
.
.
.
.
.
****-2-1
.
.
.
.
.
****-12-1
.
.
.
.
.
.
****-12-19




(****表示任意年份)


显然要排除年份的影响,生日已经过了的就要排在后面了

[cloudchen]

order by substring(replace(birthday,'-',''),5,len(birthday)) desc

[zjcxc]

--按生日排序的例子:

declare @t table(id int identity(1,1),出生日期 datetime)
insert into @t(出生日期)
select '1985-11-23'
union all select '1977-11-24'
union all select '1978-11-25'
union all select '1979-11-26'
union all select '1987-11-27'
union all select '1999-11-28'
union all select '1999-11-29'
union all select '1999-11-30'

select * from(
select *,序号=datediff(day,getdate(),dateadd(year,year(getdate())-year(出生日期),出生日期))
from @t) a
order by case when 序号=0 then 3 when 序号>0 then 1 when 序号<1 then 2 end,序号

[dlpseeyou]

select rq from
(select '2000-12-04' rq
union select '2001-01-02'
union select '2000-09-01'
union select '2003-02-05') a
order by replace(rq,'-','') desc

[victorycyz]

写了一个SQL语句，测试通过。ddate为出生日期的字段名。

select * from table1 order by (case when (datepart("dy",ddate)-datepart("dy",getdate()))>0 then datepart("dy",ddate) else datepart("dy",cast(cast(datepart("yyyy",getdate()) as char(4))+'/'+'12/31' as smalldatetime))+datepart("dy",ddate) end )

测试数据的排序结果：
1899-12-30 15:00:00.000
1899-12-30 08:00:00.000
1900-01-01 17:00:00.000
1900-01-01 17:00:00.000
1900-01-01 17:00:00.000
2002-08-21 05:00:00.000
2002-08-21 10:05:00.000
2002-08-21 15:00:00.000
2002-08-22 13:00:00.000

谢谢两位靓妹程序员在解决这个问题中提供的帮助。

[pengdali]

select * from (
select *,datediff(day,rtrim(year(getdate()))+right(CONVERT(char(10),birthday,120),6),getdate()) tem from
Member) aa order by case when tem>0 then 1 else 0 end ,tem desc

[victorycyz]

上面>0应该是>=0

刚才的测试数据不太典型,再加一些数据测试:

select * from table1 order by (case when (datepart("dy",ddate)-datepart("dy",getdate()))>=0 then datepart("dy",ddate) else datepart("dy",cast(cast(datepart("yyyy",getdate()) as char(4))+'/'+'12/31' as smalldatetime))+datepart("dy",ddate) end )

1955-11-27 00:00:00.000
1955-11-27 00:00:00.000
1885-11-28 00:00:00.000
1885-11-28 00:00:00.000
1972-12-10 00:00:00.000
1972-12-10 00:00:00.000
2003-12-26 00:00:00.000
1899-12-30 15:00:00.000
1899-12-30 08:00:00.000
1900-01-01 17:00:00.000
1900-01-01 17:00:00.000
1900-01-01 17:00:00.000
2002-08-21 05:00:00.000
2002-08-21 10:05:00.000
2002-08-21 15:00:00.000
2002-08-22 13:00:00.000
1985-10-20 00:00:00.000
1985-10-20 00:00:00.000
1999-11-26 00:00:00.000
1999-11-26 00:00:00.000

[playyuer]

order by month(birthday),day(birthday)

[playyuer]

declare @ datetime
set @ = getdate()

select *
from t
where datediff(day,dateadd(year,datediff(year,birthday,@),birthday),@) >=0
order by dateadd(year,datediff(year,birthday,@),birthday)

[playyuer]

declare @ datetime
set @ = getdate()

select *
from t
where datediff(day,@,dateadd(year,datediff(year,birthday,@),birthday)) >=0
order by dateadd(year,datediff(year,birthday,@),birthday)

[gmlxf]

-- test

create table member
(
birthday datetime
)
insert member
select '1984-12-19'
union select '1994-12-25'
union select '1964-12-18'
union select '1984-05-12'
union select '1984-06-06'
union select '1954-10-12'
union select '1994-01-25'
union select '1958-09-22'

select * from member
order by
case when datediff(d,getdate(),convert(datetime,stuff(convert(varchar,birthday,120),1,4,'2003'),120))>=0 then
datediff(d,getdate(),convert(datetime,stuff(convert(varchar,birthday,120),1,4,'2003'),120))-1000
else
datediff(d,getdate(),convert(datetime,stuff(convert(varchar,birthday,120),1,4,'2003'),120))
end

drop table member

--结果
/*
birthday
------------------------------------------------------
1984-12-19 00:00:00.000
1994-12-25 00:00:00.000
1994-01-25 00:00:00.000
1984-05-12 00:00:00.000
1984-06-06 00:00:00.000
1958-09-22 00:00:00.000
1954-10-12 00:00:00.000
1964-12-18 00:00:00.000
（所影响的行数为 8 行）
*/