id state userid addtime
1 1 3 2009-12-17
2 1 3 2009-12-16
3 1 2 2009-12-15
4 0 3 2009-12-14
5 0 2 2009-12-16
6 0 2 2009-12-15
如何得到如下结果
id state userid addtime
1 1 3 2009-12-14
2 1 3 2009-12-13
5 0 2 2009-12-16
3 1 2 2009-12-15
6 0 2 2009-12-15
4 0 3 2009-12-14用户3 同时 STATE 1 排在最前 其他按日期排序
1 1 3 2009-12-17
2 1 3 2009-12-16
3 1 2 2009-12-15
4 0 3 2009-12-14
5 0 2 2009-12-16
6 0 2 2009-12-15
如何得到如下结果
id state userid addtime
1 1 3 2009-12-14
2 1 3 2009-12-13
5 0 2 2009-12-16
3 1 2 2009-12-15
6 0 2 2009-12-15
4 0 3 2009-12-14用户3 同时 STATE 1 排在最前 其他按日期排序
from table1
order by case when userid=3 and state=1 then 0 else 1 end,addtime desc
tb
order by case when state=1 and userid=3 then 0 else 1 end
tb
order by case when state=1 and userid=3 then 0 else 1 end,addtime desc
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-28 20:22:55
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[state] int,[userid] int,[addtime] datetime)
insert [tb]
select 1,1,3,'2009-12-17' union all
select 2,1,3,'2009-12-16' union all
select 3,1,2,'2009-12-15' union all
select 4,0,3,'2009-12-14' union all
select 5,0,2,'2009-12-16' union all
select 6,0,2,'2009-12-15'
--------------开始查询--------------------------
select
*
from
tb
order by
case when userid=3 and state=1 then 0 else 1 end,addtime desc
----------------结果----------------------------
/* id state userid addtime
----------- ----------- ----------- -----------------------
1 1 3 2009-12-17 00:00:00.000
2 1 3 2009-12-16 00:00:00.000
5 0 2 2009-12-16 00:00:00.000
6 0 2 2009-12-15 00:00:00.000
3 1 2 2009-12-15 00:00:00.000
4 0 3 2009-12-14 00:00:00.000(6 行受影响)*/
from table1
order by case when userid=3 and state=1 then 0 else 1 end,addtime desc
from table1
order by case when userid=3 and state=1 then 0 else 1 end,addtime desc