22,209
社区成员
发帖
与我相关
我的任务
分享SQL 排序问题 急!! 大虾快进
原排序多条件
如:select top 100 * from table order by gold desc,expire_date desc
gold是等级 expire_date是到期时间
根据前面的查询是按等级后再按到期时间,这样的话高等级的过期的人就在低等级没过期的人的前面.
现在要做成 过期的都要到后面显示 无论等级是多少 而没过期的还是正常按等级及到期时间排列
急救~~~~~~
如:select top 100 * from table order by gold desc,expire_date desc
gold是等级 expire_date是到期时间
根据前面的查询是按等级后再按到期时间,这样的话高等级的过期的人就在低等级没过期的人的前面.
现在要做成 过期的都要到后面显示 无论等级是多少 而没过期的还是正常按等级及到期时间排列
急救~~~~~~
...全文
请发表友善的回复…
发表回复
tenson79 2006-06-29
- 打赏
- 举报
谢谢 楼上各位 ~~~ 小D 羡慕啊
fcuandy 2006-06-29
- 打赏
- 举报
100分被鱼抢了。呵呵
paoluo 2006-06-29
- 打赏
- 举报
sxycgxj(云中客) ( ) 信誉：100 2006-06-29 16:38:00 得分: 0
select top 100 * from table whereexpire_date>=getdate()
order by expire_date desc,gold desc
union all
select top 100 * from table where expire_date<getdate()
----------------------
語句會報錯，改為
Select * From
(select top 100 * from TEST where expire_date>=getdate()
order by gold desc, expire_date desc) A
union all
Select * From
(select top 100 * from TEST where expire_date<getdate()
order by gold desc,expire_date desc) A
select top 100 * from table whereexpire_date>=getdate()
order by expire_date desc,gold desc
union all
select top 100 * from table where expire_date<getdate()
----------------------
語句會報錯，改為
Select * From
(select top 100 * from TEST where expire_date>=getdate()
order by gold desc, expire_date desc) A
union all
Select * From
(select top 100 * from TEST where expire_date<getdate()
order by gold desc,expire_date desc) A
LouisXIV 2006-06-29
- 打赏
- 举报
假设 expire_date记录的是日期，且以当前时间为基准判断是否过期
select top 100 *
from tablename
order by
(case when datediff(day,expire_date,getdate())>0 then 0 else 1 end),
gold desc
select top 100 *
from tablename
order by
(case when datediff(day,expire_date,getdate())>0 then 0 else 1 end),
gold desc
paoluo 2006-06-29
- 打赏
- 举报
Create Table TEST
(ID Int,
gold Int,
expire_date DateTime)
Insert TEST Select 1,2,'2006-06-30'
Union All Select 2,3,'2006-06-30'
Union All Select 3,5,'2006-05-30'
Union All Select 4,6,'2006-06-12'
Union All Select 5,10,'2006-07-30'
Union All Select 6,1,'2006-08-30'
GO
select top 100 * from [TEST] order by (Case When expire_date>=GetDate() Then 0 Else 1 End), gold desc,expire_date desc
GO
Drop Table TEST
--Result
/*
ID gold expire_date
5 10 2006-07-30 00:00:00.000
2 3 2006-06-30 00:00:00.000
1 2 2006-06-30 00:00:00.000
6 1 2006-08-30 00:00:00.000
4 6 2006-06-12 00:00:00.000
3 5 2006-05-30 00:00:00.000
*/
(ID Int,
gold Int,
expire_date DateTime)
Insert TEST Select 1,2,'2006-06-30'
Union All Select 2,3,'2006-06-30'
Union All Select 3,5,'2006-05-30'
Union All Select 4,6,'2006-06-12'
Union All Select 5,10,'2006-07-30'
Union All Select 6,1,'2006-08-30'
GO
select top 100 * from [TEST] order by (Case When expire_date>=GetDate() Then 0 Else 1 End), gold desc,expire_date desc
GO
Drop Table TEST
--Result
/*
ID gold expire_date
5 10 2006-07-30 00:00:00.000
2 3 2006-06-30 00:00:00.000
1 2 2006-06-30 00:00:00.000
6 1 2006-08-30 00:00:00.000
4 6 2006-06-12 00:00:00.000
3 5 2006-05-30 00:00:00.000
*/
云中客 2006-06-29
- 打赏
- 举报
select top 100 * from table whereexpire_date>=getdate()
order by expire_date desc,gold desc
union all
select top 100 * from table where expire_date<getdate()
order by expire_date desc,gold desc
union all
select top 100 * from table where expire_date<getdate()
itblog 2006-06-29
- 打赏
- 举报
贴点测试数据吧，很难想像的出来！·
paoluo 2006-06-29
- 打赏
- 举报
select top 100 * from [table] order by (Case When expire_date>=GetDate() Then 0 Else 1 End), gold desc,expire_date desc
LouisXIV 2006-06-29
- 打赏
- 举报
过期与否是怎么判断的？？
playwarcraft 2006-06-29
- 打赏
- 举报
加個where條件,把過期的過慮掉
where expire_date>=getdate()
where expire_date>=getdate()
itblog 2006-06-29
- 打赏
- 举报
select top 100 * from table order by expire_date desc,gold desc
tenson79 2006-06-29
- 打赏
- 举报
急救~~~~~~
