现我在只能在应用程序里面处理,先是查带有xx的数据,然后跟别的连接起来,先是select name from table where left(name,2)='xx' order by name 查出来的结果放在一个ArrayList里面(我是用java来写的),再用select name from table where left(name,2)<>'xx' order by name放在别一个ArrayList里面,然后用两个ArrayList连接起来,但我觉得这样太麻烦了,请问一下高手们有没有能用一句sql语句能实现这个功能。
select name from table where left(name,2)='xx' order by name union select name from table where left(name,2)<>'xx' order by name哈哈
yangxiao_jiang(哈哈) ,mysql 语法不支持select name from table where left(name,2)='xx' order by name union select name from table where left(name,2)<>'xx' order by name 但如果写成:select name from table where left(name,2)='xx' union select name from table where left(name,2)<>'xx' order by name 那又跟select name from table order by name 没什么两样。有更好的办法吗?
用(select name from table where left(name,2)='xx' order by name) union (select name from table where left(name,2)<>'xx' order by name)是可以查的,但排序失效,最后的结果虽然带xx的在前面,但并没有按name排序,两部分都是按id字段来排的。
select * from 表名 order by case when left(name,2)='xx' then 1 else 2 end, name
union
select name from table where left(name,2)<>'xx' order by name哈哈
union
select name from table where left(name,2)<>'xx' order by name
但如果写成:select name from table where left(name,2)='xx'
union
select name from table where left(name,2)<>'xx' order by name
那又跟select name from table order by name 没什么两样。有更好的办法吗?
from 表名
order by case when left(name,2)='xx' then 1 else 2 end, name