SQL排序问题。。。

[ppsniffer]

SELECT TOP 10 * FROM [book] where bookid in (1065003,1067113,1039748,1067630,1066993,1061085,1060138,1058105,1063632,1056890,1064456,1066585,1062870,1055856) 

查询结果怎样根据()括号里面的顺序排序？

比如ID为1065003排第一，1067113第二这样。。

[ljking0731]

SELECT TOP 10 * FROM [book] where bookid in (1065003,1067113,1039748,1067630,1066993,1061085,1060138,1058105,1063632,1056890,1064456,1066585,1062870,1055856) 
order by charindex(','+bookid+',',',1065003,1067113,1039748,1067630,1066993,1061085,1060138,1058105,1063632,1056890,1064456,1066585,1062870,1055856,')

[ljking0731]

SELECT TOP 10 * FROM [book] where bookid in (1065003,1067113,1039748,1067630,1066993,1061085,1060138,1058105,1063632,1056890,1064456,1066585,1062870,1055856) 
order by charindex(','+cast(bookid as varchar)+',',',1065003,1067113,1039748,1067630,1066993,1061085,1060138,1058105,1063632,1056890,1064456,1066585,1062870,1055856,')

[rucypli]

直接order by bookid

[haitao]

直接加上order by bookid不行吗？

[Shawn]

create table #BOOK
(
	bookid int,
	[name] varchar(10)
) 
insert #BOOK(bookid)
select 1066993 union all
select 1061085 union all
select 1067113 union all
select 1039748 union all
select 1064456 union all
select 1066585 union all
select 1060138 union all
select 1058105 union all
select 1063632 union all
select 1067630 union all
select 1056890 union all
select 1066993 union all
select 1055856
go
--SQL:
;with cte as
(
	select rowno = ROW_NUMBER() over(order by getdate()), * from
	(
		select bookid = 1065003 union all
		select 1067113 union all
		select 1039748 union all
		select 1067630 union all
		select 1066993 union all
		select 1061085 union all
		select 1060138 union all
		select 1058105 union all
		select 1063632 union all
		select 1056890 union all
		select 1064456 union all
		select 1066585 union all
		select 1062870 union all
		select 1055856
	) t
)
select a.* from
(SELECT TOP 10 * FROM #BOOK where bookid in (select bookid from cte)) a
inner join cte b
on a.bookid = b.bookid
order by b.rowno

[快溜]

何必呢。。

[无水先生]

SELECT TOP 10 * FROM [book] where bookid in (1065003,1067113,1039748,1067630,1066993,1061085,1060138,1058105,1063632,1056890,1064456,1066585,1062870,1055856) ording by bookid;

[yishengjihe]