剛剛又來了一條:We can gernalize this with a seocnd albe of the sort ordering:CREATE TABLE SortOrder
(foo_key INTEGER NOT NULL PRIMARY KEY,
sort INTEGER NOT NULL UNIQUE);INSERT INTO SortOrder VALUES (5, 1);
INSERT INTO SortOrder VALUES (3, 2);
INSERT INTO SortOrder VALUES (4, 3);
INSERT INTO SortOrder VALUES (2, 4);thenSELECT Foobar.*, S1.sort
FROM Foobar, SortOrder AS S1
WHERE S1.foo_key = Foobar.foo_key
ORDER BY S1.sort;--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
我的英文很差,半多的意思都不太懂。(呵呵~~~~~)
(foo_key INTEGER NOT NULL PRIMARY KEY,
sort INTEGER NOT NULL UNIQUE);INSERT INTO SortOrder VALUES (5, 1);
INSERT INTO SortOrder VALUES (3, 2);
INSERT INTO SortOrder VALUES (4, 3);
INSERT INTO SortOrder VALUES (2, 4);thenSELECT Foobar.*, S1.sort
FROM Foobar, SortOrder AS S1
WHERE S1.foo_key = Foobar.foo_key
ORDER BY S1.sort;--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
我的英文很差,半多的意思都不太懂。(呵呵~~~~~)
PFPF
create table tblName (ID int, Name varchar(8))
insert tblName values (12, 'Nipsan')
insert tblName values (1, 'Jeff')
insert tblName values (11, 'Chow')
insert tblName values (5, 'CYL')
insert tblName values (21, 'Allen')
假设你的输入:'12,21,1,11,5,......'我们创建一个临时表按用户输入的Like条件的顺序依次保存纪录:
create table #tmpOrderBy(orderid int IDENTITY(1, 1),orderval int)
类似的语句:
insert #tmpOrderBy(orderval) values(12)
insert #tmpOrderBy(orderval) values(21)
......最后连接你的表和临时表:
select @strSQL='select A.* from tblName as A inner join #tmpOrderBy as B on A.id=B.orderval order by B.orderid'
execute(@strSQL)完整代码:declare @strLike varchar(2000)
declare @strSQL varchar(2000)
declare @a varchar(50)select @strLike='12,21,1,11,5'create table #tmpOrderBy(orderid int IDENTITY(1, 1),orderval int)
while(len(@strLike)>0)
begin if CHARINDEX(',',@strLike)=0
begin
insert #tmpOrderBy(orderval) values(@strLike)
break
end
else
begin
select @a=substring(@strLike,1,CHARINDEX(',',@strLike)-1)
insert #tmpOrderBy(orderval) values(@a)
select @strLike=substring(@strLike,CHARINDEX(',',@strLike)+1,len(@strLike)-CHARINDEX(',',@strLike))
end
endselect @strSQL='select A.* from tblName as A inner join #tmpOrderBy as B on A.id=B.orderval order by B.orderid'execute(@strSQL)
drop table #tmpOrderBy当@strLike='12,21,1,11,5'时,返回:
ID Name
----------- --------
12 Nipsan
21 Allen
1 Jeff
11 Chow
5 CYL当@strLike='21,1,11,12,5',返回:
ID Name
----------- --------
21 Allen
1 Jeff
11 Chow
12 Nipsan
5 CYL
當初我把這個問題發到News group上就是想找找有沒有更好的解決方法。
SELECT *
FROM tblName
WHERE ID IN (...)
ORDER BY
CASE ID
WHEN x THEN 1
......
END
这样的SQL语句不就行了:
VB例子:
strLike = "5,11,21,1,12"
strSQL = "select * from tblName where id in (" & strLike & ") order by case id"
i = 1
tmp = ""
Do While Len(strLike) > 0
If InStr(1, strLike, ",") = 0 Then
strSQL = strSQL & " when " & strLike & " then " & i
Exit Do
Else
tmp = Left(strLike, InStr(1, strLike, ",") - 1)
strSQL = strSQL & " when " & tmp & " then " & i
strLike = Right(strLike, Len(strLike) - InStr(1, strLike, ","))
End If
i = i + 1
Loop
strSQL = strSQL & " end"这个sql语句为:
select * from tblName
where id in (5,11,21,1,12)
order by
case id
when 5 then 1
when 11 then 2
when 21 then 3
when 1 then 4
when 12 then 5
end不过执行的效率就不是很好
http://www.csdn.net/expert/topic/374/374586.shtm
declare @strLike varchar(200)
select @strLike='775,1001,807,779' --用户查询条件
select @strLike='0,'+@strLike --注意这里,我们需要在用户查询的条件前添加一个,declare @strSQL varchar(2000)select @strSQL='select pr_no from procurement_Requisition where pr_no in ('+@strLike+') order by charindex('',''+convert(varchar(50),pr_no),'''+@strLike+''')'print @strSQL
--返回的SQL语句为:
--select pr_no from procurement_Requisition where pr_no in (0,775,1001,807,779) order by charindex(','+convert(varchar(50),pr_no),'0,775,1001,807,779')exec(@strSQL)