怎么按照IN语句里的顺序进行排序

如何按照IN语句里的顺序进行排序
SELECT
  *
FROM
  tab
WHERE
  price>5.0
  AND
  price<99.0
  AND
  id IN (31132,47100,35675,34210,35119,37173,37394,45293,45310,32570,36533,34140,35586,45276,45284,45303,42480,42498,38318,39710,41485,42573,26838,41785,42089,38522,40519,43023,43719,38303,40456,40638,40838,34262,35186,27546,27549,27550,27554,28321,28323,28332,26652,37719,42516,43286,43935,43938,26024,26811,27708,28368,37603,40437,43239,43342,43809,43978,25996,26023,26145,26151,26289,26417,26420,27107,27116,27146,27502,27522)

如何按照IN里面那些id的顺序排列呢？

我用的是 ORDER BY CASE id WHEN 31132 THEN 1 WHEN 47100 THEN 2 WHEN 35675 THEN 3.....END
感觉效率不高。

因为有的时候在IN里面的id有几万个

[解决办法]

SQL code

这么长的in..CREATE TABLE #T(id int identity,val int)INSERT #T(val) VALUES(31132)INSERT #T(val) VALUES(47100)select *from tab as a  join #T as b     on a.id=B.valwhere a.price<99.0order by b.id
[解决办法]
类似这样：
SQL code
order by charindex(','+ltrim(id)+',',',31132,47100,35675,34210,35119,')
[解决办法]
SQL code
SELECT   * FROM   tab WHERE     price>5.0   AND     price <99.0   AND   id IN (31132,47100,35675,34210,35119,37173,37394,45293,45310,32570,36533,34140,35586,45276,45284,45303,42480,42498,38318,39710,41485,42573,26838,41785,42089,38522,40519,43023,43719,38303,40456,40638,40838,34262,35186,27546,27549,27550,27554,28321,28323,28332,26652,37719,42516,43286,43935,43938,26024,26811,27708,28368,37603,40437,43239,43342,43809,43978,25996,26023,26145,26151,26289,26417,26420,27107,27116,27146,27502,27522)ORDER BY CHARINDEX(','+LTRIM(ID)+',',',31132,47100,35675,34210,35119,37173,37394,45293,45310,32570,36533,34140,35586,45276,45284,45303,42480,42498,38318,39710,41485,42573,26838,41785,42089,38522,40519,43023,43719,38303,40456,40638,40838,34262,35186,27546,27549,27550,27554,28321,28323,28332,26652,37719,42516,43286,43935,43938,26024,26811,27708,28368,37603,40437,43239,43342,43809,43978,25996,26023,26145,26151,26289,26417,26420,27107,27116,27146,27502,27522,')
[解决办法]
先建临时表
１．如果无重复ID的话且长度小于10000的话
写入用字符串 用CHARINDEX（5楼）来做
２．如果有重复ID的话
建一个序列sq来建数据序列
再从临时表里取出

[解决办法]
SQL code
----测试表名loginlogselect loginlog.loginid,loginlog.userid ----这里是你要查询的字段列表from (select 197 as loginid,1 as idunion select 188,2union select 195,3----你有多少条件就union上多少条件，但这个临时表的id必需有序，) as temptable 临时union一个表,loginlog ----要查询的表名where temptable.loginid=loginlog.loginid and loginlog.userid='libeibei'----这里加你想要的条件order by temptable.id