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有几万个
-------------------------------------------------------------------------------------------------------------------------------------------------------
drop table #t
CREATE TABLE #t(
a INT,
b VARCHAR(20),
c DECIMAL(18,2)
)
INSERT #t(a,b,c)
SELECT 1,'x',1.11
UNION ALL
SELECT 2,'y',2.22
UNION ALL
SELECT 3,'x',3.3
select * from #t order by charindex(','+LTRIM(a)+',',',2,3,1,')
/*
2 y 2.22
3 x 3.30
1 x 1.11
*/
--------------------------------------------------------------------------------------------------------
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,')
本文作者:不详 来源:网络
CIO之家 www.ciozj.com 微信公众号:imciow