• 主页
• 基础类
• 应用实例
• 新技术前沿

# 高难度排序问题，急寻帮助！

tt3125 2007-12-19 11:20:55

ID
cde
cdeid
pid

ID cde cdeid pid
1 AAA 2 2
2 DDD 1 3
3 CCC 1 1
4 DDD 3 1
5 DDD 2 1
6 DDD 1 2

1、获取cde=DDD数据并且优先cdeid按从大到小排序然后pid按小到大排序
2、cde不等于DDD的数据cdeid不排序，pid按小到大排序

ID cde cdeid pid
4 DDD 3 1
5 DDD 2 1
6 DDD 1 2
2 DDD 1 3
3 CCC 1 1
1 AAA 2 2

1、
select cde,cdeid,pid from TTT where cde="&request("cde")&" order by cdeid desc,pid asc

2、
select cde,cdeid,pid from TTT order by pid asc

...全文
68 点赞 收藏 4

4 条回复

bqb 2007-12-19
create table test(ID int, cde varchar(20), cdeid int, pid int)
insert into test
select 1 , 'AAA' , 2 , 2 union all
select 2 , 'DDD' , 1 , 3 union all
select 3 , 'CCC' , 1 , 1 union all
select 4 , 'DDD' , 3 , 1 union all
select 5 , 'DDD' , 2 , 1 union all
select 6 , 'DDD' , 1 , 2

select * from test
order by
case when cde='DDD' then '1' else '0' end+
case when cde='DDD' then convert(char(1),cdeid) else '0' end desc,pid

/*
ID cde cdeid pid
--------------------------------------------------
4 DDD 3 1
5 DDD 2 1
6 DDD 1 2
2 DDD 1 3
3 CCC 1 1
1 AAA 2 2
*/

tim_spac 2007-12-19

cde="&request("cde")&" order by cdeid desc,pid asc
cde!="&request("cde")&" order by pid asc

select cde,cdeid,pid from TTT order by
case cde!="&request("cde")&" then -1 else cdeid end desc, pid asc

chuifengde 2007-12-19
``select * from TTT order by case when cde='DDD' then 0 else 1 end,case when cde='DDD' then cdeid else 0 end desc,pid``

``````select
cde,cdeid,pid
from
TTT
order by
(case cde when 'DDD' then cdeid else 0 end) desc,pid asc ``````

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区