Question

请教: SQL中的union结果优先排序问题

sql语句是
sql="select * fromnews where title like '亲人' union select * from news where title like '朋友' order by id asc "

需要实现的结果是：显示的记录中“亲人”的结果出现在“朋友”前面 就是第一个SQL结果优先显示

因为是ACCESS数据库 下面这种方式没有通过
sql="select 1 fuhao,* fromnews where title like '亲人' union select 2 fuhao,* from news where title like '朋友' order by fuhao , id "

请教高人 小弟感谢中 ......
1 楼的方法测试没有成功

sql="select 1 fuhao,* fromnews where title like '亲人' union select 2 fuhao,* from news where title like '朋友' order by fuhao , id "

感觉要是能实现区分两个SQL的 并且标识下大小 按大小排 应该能实现 期待高人 ...

Answer 1

加入 NEWS 表里有 名字，年龄，生日 那么：
select * from
(
select 1 as 顺序 ,名字,年龄,生日 from news where title like '%亲人%'
union all
select 2 as 顺序,名字,年龄,生日 from news where title like '%朋友%'
) a
order by 顺序 -- 想家其他字段 就 再加上

Answer 2

sql="declare @t table(id int,title varchar(100),fuhao varchar(100),其它列1 varchar(100),其它列2 int)insert into @t select * from news order by id select * from @t where title like '%亲人%' union select * from @t where title like '%朋友%' "

楼主请将(id int,title varchar(100),fuhao varchar(100),其它列1 varchar(100),其它列2 int)改成你的news表的结构

Answer 3

SQL中的union结果优先排序问题
select * from news where title like '亲人' union select * from news where title like '朋友' order by title, id asc

Answer 4

select * fromnews where title like '亲人' union select * from news where title like '朋友' order by id asc
自己定义一个排序的列
select * from (select 1 as sort, * from news where title like '亲人'
union select 2 as sort, * from news where title like '朋友' ) t
order by t.sort

Answer 5

select * from news where title like '亲人'
union select * from news
where title like '朋友'
order by title, id asc