22,210
社区成员
发帖
与我相关
我的任务
分享如何按照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有几万个
*
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有几万个
...全文
请发表友善的回复…
发表回复
genie007 2009-04-30
- 打赏
- 举报
如果IN里面的ID有几十万个,那么这个SQL语句肯定不会是手工写出来的,用临时表按插入ID的顺序比较好
successhai 2009-04-29
- 打赏
- 举报
in()中的值最多支持1000个，oracle10g中是。其他的应该也是
这个问题临时表应该正解
order by charindex(','+ltrim(id)+',',',31132,47100,35675,34210,35119,') 的方法效率低吧
这个问题临时表应该正解
order by charindex(','+ltrim(id)+',',',31132,47100,35675,34210,35119,') 的方法效率低吧
越过越咸 2009-04-29
- 打赏
- 举报
有问题啊 至少我还在回帖 回帖是一种美德！每天回帖即可获得 10 分可用分！
有这样写的吗？
有这样写的吗？
LichKing 2009-04-28
- 打赏
- 举报
study
xiaoxiangqing 2009-04-28
- 打赏
- 举报
用临时表比较好.
xuerufan 2009-04-28
- 打赏
- 举报
order by charindex(','+ltrim(id)+',',',31132,47100,35675,34210,35119,')
qgylovelj 2009-04-28
- 打赏
- 举报
bu cuo
冰岛男孩 2009-04-28
- 打赏
- 举报
这问题比较BC啊……
shenqing820701 2009-04-28
- 打赏
- 举报
这问题比较BC啊……
kelvin_yuwenjing 2009-04-28
- 打赏
- 举报
学习下
dxy2798 2009-04-28
- 打赏
- 举报
[Quote=引用 42 楼 dinoalex 的回复:]
把INI里的一个一个按顺序读出,并写进一个临时表#A(两个字段,第一个是递增字段)里,
那么你查询时就可以ID IN (SELECT INIID FROM #A).再JOIN 那个递增字段.就可以根据递增字段的值排序了
[/Quote]
是个好的办法。
把INI里的一个一个按顺序读出,并写进一个临时表#A(两个字段,第一个是递增字段)里,
那么你查询时就可以ID IN (SELECT INIID FROM #A).再JOIN 那个递增字段.就可以根据递增字段的值排序了
[/Quote]
是个好的办法。
milk111 2009-04-28
- 打赏
- 举报
顶下
net205 2009-04-27
- 打赏
- 举报
学习....
x114944880 2009-04-27
- 打赏
- 举报
不就是按ID排序吗？
kumico 2009-04-27
- 打赏
- 举报
[Quote=引用 1 楼 liangCK 的回复:]
SQL code这么长的in..CREATETABLE#T(idintidentity,valint)INSERT#T(val)VALUES(31132)INSERT#T(val)VALUES(47100)select*fromtabasajoin#Tasbona.id=B.valwherea.price<99.0orderbyb.id
[/Quote]
就是这个思路！
SQL code这么长的in..CREATETABLE#T(idintidentity,valint)INSERT#T(val)VALUES(31132)INSERT#T(val)VALUES(47100)select*fromtabasajoin#Tasbona.id=B.valwherea.price<99.0orderbyb.id
[/Quote]
就是这个思路！
zhang409497873 2009-04-27
- 打赏
- 举报
满纽曼书里欧
yangjue1 2009-04-27
- 打赏
- 举报
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,')
*
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,')
quekong 2009-04-27
- 打赏
- 举报
非常不错，谢谢分享
jiangdong5205 2009-04-26
- 打赏
- 举报
学习啦```
aishen6 2009-04-26
- 打赏
- 举报
不懂也 能详细点吗
加载更多回复（60）
