如果只有这四个值还比较好办:
select 1 as forder ,*
from user
where id=3
union
(select 2 as forder ,*
from user
where id=7)
union
(select 3 as forder ,*
from user
where id=8)
union
(select 4 as forder ,*
from user
where id=2)
order by forder
若不止这四个值,可能你需要再建一个表(t_order)
forder fvalue
1 3
2 7
3 8
4 2select t1.*
from user join t_order on user.id=t_order.fvalue
order by t_order.forder
select 1 as forder ,*
from user
where id=3
union
(select 2 as forder ,*
from user
where id=7)
union
(select 3 as forder ,*
from user
where id=8)
union
(select 4 as forder ,*
from user
where id=2)
order by forder
若不止这四个值,可能你需要再建一个表(t_order)
forder fvalue
1 3
2 7
3 8
4 2select t1.*
from user join t_order on user.id=t_order.fvalue
order by t_order.forder
union
select * from user where id=7
union
select * from user where id=8
union
select * from user where id=2
一点小建议,组成字符串的时候最好用分隔符各个数值,如
3,7,8,2组成字符串"3-7-8-2"或者"3,7,8,2",这样的话就不会出现因为前后数字连在一起发生错误了。
select * from user
where instr(v_list, id) > 0
order by instr(v_list,id)
select userid from userinfo where userid in (12,1,11) order by instr('12,1,11',userid)
结果是1,12,11这样排序阿!,还是先找到了1的位置
select userid from userinfo
where userid in (12,1,11) and
int(instr('120111',userid)/2)<>instr('120111',userid)/2
order by instr('120111',userid)
select userid from userinfo
where userid in (12,1,11) and
instr('120111',userid)%2=1
order by instr('120111',userid)
userid from userinfo where userid in (12,1,11) order by x
userid from userinfo where userid in (12,1,11,) order by x
仔细想想我的也不对.贴主快点说是什么数据库吧,因为各种数据库函数不一样的!
这样吧:
select instr('[12][1][11]','['+cast(userid as char)+"]") as x,
userid from userinfo where userid in (12,1,11) order by x 这样应该不会错了:
其实用我的方法也可以,就是不加分隔符,只是SQL的函数不记得,不敢瞎写,写个ACCESS的吧:
select userid
from userinfo
where mod(instr('120111',format(userid,"00")),2)=1
order by instr('120111',format(userid,"00"))
progame:这样不对的,因为1在这里第一个字符就匹配了,返回是1,而正确的应是返回4.
仔细想想我的也不对.贴主快点说是什么数据库吧,因为各种数据库函数不一样的!
说的是你的第一种方法!
WHERE
SecID IN (1107901,522487,859150)
ORDER BY
CHARINDEX(CAST(SecID AS VARCHAR)+',','1107901,522487,859150')
WHERE
SecID IN (1107901,522487,859150)
ORDER BY
CHARINDEX(CAST(SecID AS VARCHAR)+',','1107901,522487,859150,') --少了個,號
用分隔符
select userid
from userinfo
where userid in (12,1,11)
order by charindex('['+cast(userid as varchar)+']','[12][1][11]') 用定长字符串还是不行,会有问题:-(还是用分隔符的好。
另外,可以采用MSSQL的CASE句法。
select *,
CASE ID
WHEN 3 THEN 1
WHEN 7 THEN 2
WHEN 8 THEN 3
WHEN 2 THEN 4
ELSE 99
END
AS ListOrder
from user where id in(3,7,8,2)
ORDER BY ListOrder