SQL按照两个字段排序，其中一个字段优先排序

position表
id positiontype addtime
1   1           2008-8-09
2   2           2008-5-09
3   3           2008-5-09
4   3           2008-8-09

通过一个语句得到下面结果
id positiontype addtime
3   3           2008-5-09
4   3           2008-8-09
2   2           2008-5-09
1   1           2008-8-09

描述：
无论怎样positiontype为3的一直排在最前面，而且要按照时间倒序排列。 positiontype除了3之外的就按照时间倒序。
数据库类型为sql 2000

11 个解决方案

#1

select * from position where positiontype=3
union all
select * from position where positiontype<>3 order by addtime desc

#2

select * from position order by (case positiontype when 3 then 1 else 2 end),addtime desc

#3

--try
select * from position
order by case positiontype when 3 then 0 else datediff(d, addtime, '2038-01-01') end

#4

positiontype除了3之外的就按照时间倒序

这样说..positiontype=1的应该在2的前面..

#5

--> By dobear_0922(小熊) 2008-11-14 10:10:12
--> 测试数据：[position]
if object_id('[position]') is not null drop table [position]
create table [position]([id] int,[positiontype] int,[addtime] datetime)
insert [position]
select 1,1,'2008-8-09' union all
select 2,2,'2008-5-09' union all
select 3,3,'2008-5-09' union all
select 4,3,'2008-8-09'

select * from [position] 
order by case positiontype when 3 then 0 else datediff(d, addtime, '2038-01-01') end 

/*
id          positiontype addtime
----------- ------------ -----------------------
3           3            2008-05-09 00:00:00.000
4           3            2008-08-09 00:00:00.000
1           1            2008-08-09 00:00:00.000
2           2            2008-05-09 00:00:00.000

(4 行受影响)
*/


drop table [position]

#6

晕，楼主的描述和给出的结果不一样

#7

引用 2 楼 libin_ftsafe 的回复:

SQL codeselect * from position order by (case positiontype when 3 then 1 else 2 end),addtime desc

#8

CREATE TABLE [dbo].[position](
[id] [int] NOT NULL,
[positiontype] [int] NOT NULL,
[addtime] [datetime] NULL,
CONSTRAINT [PK_position] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

insert position
select 1,1, '2008-8-09' union all
select 2,2, '2008-5-09'union all
select 3,3, '2008-5-09' union all
select 4,3,'2008-8-09'

select id, positiontype, addtime from position order by addtime,positiontype desc
查询结果：
id positiontype addtime
3 3 2008-05-09 00:00:00.000
2 2 2008-05-09 00:00:00.000
4 3 2008-08-09 00:00:00.000
1 1 2008-08-09 00:00:00.000

我是sql server 2005 查询没有问题.

#9

结贴学习了。感谢大家，很多语句都实现了结果， 2楼的语句最精炼，使用的函数也最少，应该是最有效率的吧，大家来评一下。

#10

if object_id('[position]') is not null drop table [position]
create table [position]([id] int,[positiontype] int,[addtime] datetime)
insert [position]
select 1,1,'2008-8-09' union all
select 2,2,'2008-5-09' union all
select 3,3,'2008-5-09' union all
select 4,3,'2008-8-09'


select * 
from position 
order by (case positiontype when 3 then 1 else 2 end),
case positiontype when 3 then addtime else dateadd(mm,datediff(d,addtime,'2008-01-01')*2, addtime) end desc

/*
id          positiontype addtime                                                
----------- ------------ ------------------------------------------------------ 
4           3            2008-08-09 00:00:00.000
3           3            2008-05-09 00:00:00.000
2           2            2008-05-09 00:00:00.000
1           1            2008-08-09 00:00:00.000

（所影响的行数为 4 行）
*/


drop table [position]

#11

---SELECT STATEMANT
select ID,positiontype,addtime, 1000 as 'sort'
from table
where positiontype='3'
union
select ID,positiontype,addtime,900 as 'sort'
where positiontype<>'3'
order by 'sort' desc, addtime desc

----TEST
select ID,positiontype,addtime, 1000 as 'sort'
from
(select 1 as id,1 as positiontype,CONVERT(datetime,'2008-8-09',101) as addtime
union
select 2 as id,2 as positiontype,CONVERT(datetime,'2008-5-09',101) as addtime
union
select 3 as id,3 as positiontype,CONVERT(datetime,'2008-5-09',101) as addtime
union
select 4 as id,3 as positiontype,CONVERT(datetime,'2008-8-09',101) as addtime
) As AA
where positiontype='3'
union
select ID,positiontype,addtime,900 as 'sort'
from
(select 1 as id,1 as positiontype,CONVERT(datetime,'2008-8-09',101) as addtime
union
select 2 as id,2 as positiontype,CONVERT(datetime,'2008-5-09',101) as addtime
union
select 3 as id,3 as positiontype,CONVERT(datetime,'2008-5-09',101) as addtime
union
select 4 as id,3 as positiontype,CONVERT(datetime,'2008-8-09',101) as addtime
) As AA
where positiontype<>'3'
order by 'sort' desc, addtime desc

#1

select * from position where positiontype=3
union all
select * from position where positiontype<>3 order by addtime desc

#2

select * from position order by (case positiontype when 3 then 1 else 2 end),addtime desc

#3

--try
select * from position
order by case positiontype when 3 then 0 else datediff(d, addtime, '2038-01-01') end

#4