Select * From DB Order By (Case When ID > 3 Then 0 Else 1 End), ID
Create Table DB (ID Int) Insert DB Select 1 Union All Select 2 Union All Select 3 Union All Select 10 Union All Select 97 Union All Select 98 Union All Select 99 GO Select * From DB Where ID > 3 Union All Select * From DB Where ID <= 3Select * From DB Order By (Case When ID > 3 Then 0 Else 1 End), ID GO Drop Table DB --Result /* ID 10 97 98 99 1 2 3 */
select * from tablename where columnname >3 union all select top 3 from tablename order by columnname asc
select * from DB order by (case when id >3 then 0 else 1 end)
TO paoluo(一天到晚游泳的鱼) : ------------------------------ 速度真够快的
--改ID的条件就行 select * from DB order by (case when id >10 then 0 else 1 end)
youbl(水边) ( ) 信誉:100 Blog 2007-04-04 14:50:21 得分: 0
好象這麼寫是可以的Create Table DB (ID Int) Insert DB Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 10 Union All Select 97 Union All Select 98 Union All Select 99 GO --方法一 Select * From DB Where ID > (Select Min(ID) From DB A Where Not Exists(Select ID From DB Where ID = A.ID + 1)) Union All Select * From DB Where ID <= (Select Min(ID) From DB A Where Not Exists(Select ID From DB Where ID = A.ID + 1))--方法二 Select * From DB Order By (Case When ID > (Select Min(ID) From DB A Where Not Exists(Select ID From DB Where ID = A.ID + 1)) Then 0 Else 1 End), ID GO Drop Table DB --Result /* ID 10 97 98 99 1 2 3 4 5 */
create table T(id int) insert into T select 1 insert into T select 2 insert into T select 3 insert into T select 10 insert into T select 11 insert into T select 97 insert into T select 98 insert into T select 99select [id] from T order by case when (select count(*) from T a where a.id<=T.id)=id then 1 else 0 end, id /* id ----------- 10 11 97 98 99 1 2 3 */drop table T
to:paoluo(一天到晚游泳的鱼) 多谢,先回去试试
to:paoluo(一天到晚游泳的鱼) 你的SQL还是有点问题,不过加两个条件就OK了,ORACLE中也能用CASE,呵呵,以前不知道 你去http://community.csdn.net/Expert/TopicView1.asp?id=5441713 也回个帖吧Select num From aa_test Order By ( Case When num > ( Select Min(num) From aa_test A Where Not Exists(Select num From aa_test Where num = A.num + 1) ) and exists(Select num From aa_test Where num = 1) and exists(Select num From aa_test Where num =9999) Then 0 Else 1 End ), num
(ID Int)
Insert DB Select 1
Union All Select 2
Union All Select 3
Union All Select 10
Union All Select 97
Union All Select 98
Union All Select 99
GO
Select * From DB Where ID > 3
Union All
Select * From DB Where ID <= 3Select * From DB Order By (Case When ID > 3 Then 0 Else 1 End), ID
GO
Drop Table DB
--Result
/*
ID
10
97
98
99
1
2
3
*/
union all
select top 3 from tablename order by columnname asc
order by (case when id >3 then 0 else 1 end)
------------------------------
速度真够快的
select * from DB
order by (case when id >10 then 0 else 1 end)
兄弟们,不行的,如果数据是这样的:
1,2,3,4,5,6,7,8,9,10,66,67,68,91,92,93,94,95,96,97,98,99那要求排序结果是:
66,67,68,91,92,93,94,95,96,97,98,99,1,2,3,4,5,6,7,8,9,10
-------------------------------------------------------------------------------
邏輯是什麼??
1-10在最後?其他在前?
select @id = 97
select * from DB
order by (case when id >@id then 0 else 1 end)
那个ID是不存在的
那要求排序结果是:
66,67,68,91,92,93,94,95,96,97,98,99,1,2,3,4,5,6,7,8,9,10如果数据是:
1,2.....96,97,99
那要求排序结果是
99,1,2......96,97
---------------------------------------------------------------------------------
逻辑就是要求实现1~99循环排序,但是不会出现99条数据都有的情况
就是如果DB中没有1或99,那么就正常排序,如果有1和99,那么1就要排在99的后面
逻辑就是要求实现1~99循环排序,但是不会出现99条数据都有的情况
就是如果DB中没有1或99,那么就正常排序,如果有1和99,那么1就要排在99的后面
--------------
還是沒看懂。斷號的數據如果超過一條,怎麼排序?
http://community.csdn.net/Expert/topic/5441/5441878.xml?temp=.5755579
(ID Int)
Insert DB Select 1
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 10
Union All Select 97
Union All Select 98
Union All Select 99
GO
--方法一
Select * From DB Where ID > (Select Min(ID) From DB A Where Not Exists(Select ID From DB Where ID = A.ID + 1))
Union All
Select * From DB Where ID <= (Select Min(ID) From DB A Where Not Exists(Select ID From DB Where ID = A.ID + 1))--方法二
Select * From DB Order By (Case When ID > (Select Min(ID) From DB A Where Not Exists(Select ID From DB Where ID = A.ID + 1)) Then 0 Else 1 End), ID
GO
Drop Table DB
--Result
/*
ID
10
97
98
99
1
2
3
4
5
*/
insert into T select 1
insert into T select 2
insert into T select 3
insert into T select 10
insert into T select 11
insert into T select 97
insert into T select 98
insert into T select 99select [id] from T
order by case when (select count(*) from T a where a.id<=T.id)=id
then 1
else 0 end, id
/*
id
-----------
10
11
97
98
99
1
2
3
*/drop table T
多谢,先回去试试
你的SQL还是有点问题,不过加两个条件就OK了,ORACLE中也能用CASE,呵呵,以前不知道
你去http://community.csdn.net/Expert/TopicView1.asp?id=5441713
也回个帖吧Select num From aa_test
Order By (
Case When num > (
Select Min(num) From aa_test A Where Not Exists(Select num From aa_test Where num = A.num + 1)
) and exists(Select num From aa_test Where num = 1) and exists(Select num From aa_test Where num =9999) Then 0 Else 1 End
), num