select top 1 id from tag where type=1 order by id desc union all select top 1 id from tag where type=0 order by id desc 比如这个语句,总是查询上面的数据是对的,如果把下面的语句放上面也能查询出来, 但是下面的语句就是不能递减排序,不知道怎么回事,请教下
You have to use the Order By at the end of ALL the unions。
the ORDER BY is considered to apply to the whole UNION result(it's effectively got lower binding priority than the UNION).
The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.
所以,只能在union的最后一个子查询中使用order by,而这个order by是针对整个unioning后的结果集的。So:
如果unoin的几个子查询列名不同,如
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY ?;
为了避免这样事情的发生,可以:
1 使用列序号代替实际列名。如:
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;
2 为unoin的各个子查询使用相同的列名,如:
Sql代码
select supplier_id as id, supplier_name as name
from suppliers
UNION
select company_id as id, company_name as name
from companies
ORDER BY name;
[Quote=引用 1 楼 wwwwb 的回复:]
select * from (
select top 1 id from tag where type=1 order by id desc )
union all
select * from (
select top 1 id from tag where type=0 order by id desc)
[/Quote]
select * from (
select top 1 id from tag where type=1 order by id desc )
union all
select * from (
select top 1 id from tag where type=0 order by id desc)