select * from ( select top 500 id,t1,t2 from a order id desc union select top 300 id,t1,t2 from b order id desc )a
select top 500 id,t1,t2 from a order id desc union all select top 300 id,t1,t2 from b order id desc
错了: select * from (select top 500 id,t1,t2 from a order by id desc)a union select * from (select top 300 id,t1,t2 from b order by id desc)b
select top 500 id,t1,t2 from a order id desc union all select top 300 id,t1,t2 from b order id desc ------------------- 在查询分析起中分析结果: 在关键字 'union' 附近有语法错误。
select * from ( select top 500 id,t1,t2 from a order id desc union select top 300 id,t1,t2 from b order id desc )
TO wangqi790202(Andersen) 还是不行! : ')' 附近有语法错误。
select * from ( select top 500 id,t1,t2 from a order by id desc union select top 300 id,t1,t2 from b order by id desc )a
select * from (select top 500 id,t1,t2 from a order by id desc)a union select top 300 id,t1,t2 from b order by id desc
select * from ( select * from (select top 500 id,t1,t2 from a order by id desc) a union select * from (select top 300 id,t1,t2 from b order by id desc) b ) c order by id desc
看来要这个效果:select id,t1,t2 from ( select * from (select top 500 id,t1,t2,1 as type from a order by id desc) a union select * from (select top 300 id,t1,t2,2 as type from b order by id desc) b ) c order by type,id desc
to lynx1111(任我行:一个PLMM看着就兴奋的男人) ( 选出来的结果还是不对!
lynx1111(任我行:一个PLMM看着就兴奋的男人) 的最后一个会出错吧?
--这是SQL的一个BUG,做下面这样一个最简单的测试就知道了.--测试环境 declare @t1 table(id int) insert @t1 select 1 union all select 2declare @t2 table(id int) insert @t2 select 1 union all select 3--查询 select top 1 * from( select top 1 id from @t1 order by id desc union all select top 1 id from @t2 order by id desc )a order by id desc/*--测试结果,显然不正确 id ----------- 1(所影响的行数为 1 行) --*/
--解决的办法就是用临时表就行了 select top 500 id,t1,t2 into #a from a order by id desc select top 300 id,t1,t2 into #b from a order by id descselect * from #a union all select * from #bdrop table #a,#b
sqlserver看来执行union之前拒绝order by,则如下: select top 500 id,t1,t2 into #t from a order by id desc select top 300 id,t1,t2 into #t1 from b order by id desc select * from #t union all select * from #t1 drop table #t drop table #t1
不用要临时表这么夸张吧??不过union 是很奇怪select * from ( select top 500 id,t1,t2 from a order by id desc union select top 300 id,t1,t2 from b order by id desc )a 没错,但取数不是我们想象的select top 500 id,t1,t2 from a order by id desc union select top 300 id,t1,t2 from b order by id desc 出错select top 500 id,t1,t2 from a union select top 300 id,t1,t2 from b order by id desc 没错,但取数不是我们想象的select top 500 id,t1,t2 from a order by id desc union select top 300 id,t1,t2 from b 出错
zjcxc(邹建) : 其实就贴在上面。--测试环境 declare @t1 table(id int) insert @t1 select 1 union all select 2declare @t2 table(id int) insert @t2 select 1 union all select 3--查询 select top 1 * from( select * from ( select top 1 id from @t1 order by id desc) as a union all select * from ( select top 1 id from @t2 order by id desc ) as b )a order by id desc--结果: id ----------- 3(所影响的行数为 1 行)
--测试环境 declare @t1 table(id int) insert @t1 select 1 union all select 2declare @t2 table(id int) insert @t2 select 1 union all select 3--查询 select * from( select * from ( select top 1 id from @t1 order by id desc) as a union all select * from ( select top 1 id from @t2 order by id desc ) as b )a order by id desc--结果 id ----------- 3 2(所影响的行数为 2 行)
嵌多一层居然就可以了,SQL真是晕.
对于SELECT 中的ORDER BY 子句 1.除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。 2.ORDER BY 子句可包含选择列表中没出现的项目,但是如果指定 SELECT DISTINCT 或 SELECT 语句包含 UNION 运算符,则排序列必须出现在选择列表中。 3.当 SELECT 语句包含 UNION 运算符时,列名或列的别名必须是在第一选择列表内指定的列名或列的别名。
我发现UNION 和UNION ALL也是不同的,在这里,一定要加 ALL 否则还是没有用! 那为知道他们的区别?
select top 500 id,t1,t2 from a order id desc
union
select top 300 id,t1,t2 from b order id desc
)a
union all
select top 300 id,t1,t2 from b order id desc
select * from (select top 500 id,t1,t2 from a order by id desc)a
union
select * from (select top 300 id,t1,t2 from b order by id desc)b
union all
select top 300 id,t1,t2 from b order id desc
-------------------
在查询分析起中分析结果:
在关键字 'union' 附近有语法错误。
select top 500 id,t1,t2 from a order id desc
union
select top 300 id,t1,t2 from b order id desc
)
还是不行!
: ')' 附近有语法错误。
select top 500 id,t1,t2 from a order by id desc
union
select top 300 id,t1,t2 from b order by id desc
)a
union
select top 300 id,t1,t2 from b order by id desc
select * from (select top 500 id,t1,t2 from a order by id desc) a
union
select * from (select top 300 id,t1,t2 from b order by id desc) b
) c
order by id desc
select * from (select top 500 id,t1,t2,1 as type from a order by id desc) a
union
select * from (select top 300 id,t1,t2,2 as type from b order by id desc) b
) c
order by type,id desc
选出来的结果还是不对!
的最后一个会出错吧?
declare @t1 table(id int)
insert @t1 select 1
union all select 2declare @t2 table(id int)
insert @t2 select 1
union all select 3--查询
select top 1 * from(
select top 1 id from @t1 order by id desc
union all
select top 1 id from @t2 order by id desc
)a order by id desc/*--测试结果,显然不正确
id
-----------
1(所影响的行数为 1 行)
--*/
select top 500 id,t1,t2 into #a from a order by id desc
select top 300 id,t1,t2 into #b from a order by id descselect * from #a
union all
select * from #bdrop table #a,#b
select top 500 id,t1,t2 into #t from a order by id desc
select top 300 id,t1,t2 into #t1 from b order by id desc select * from #t
union all
select * from #t1 drop table #t
drop table #t1
select top 500 id,t1,t2 from a order by id desc
union
select top 300 id,t1,t2 from b order by id desc
)a
没错,但取数不是我们想象的select top 500 id,t1,t2 from a order by id desc
union
select top 300 id,t1,t2 from b order by id desc
出错select top 500 id,t1,t2 from a
union
select top 300 id,t1,t2 from b order by id desc
没错,但取数不是我们想象的select top 500 id,t1,t2 from a order by id desc
union
select top 300 id,t1,t2 from b
出错
局部临时表,表名以#开头,只在创建它的过程中有效,类似程序设计中的局部变量,其他用户/过程不能访问,在创建它的过程结束后,自动删除
全局临时表,表名以##开头,在所有的过程中有效,类似程序设计中的全局变量,其他用户/过程都可以访问,在SQL服务结束时,自动删除.
其实就贴在上面。--测试环境
declare @t1 table(id int)
insert @t1 select 1
union all select 2declare @t2 table(id int)
insert @t2 select 1
union all select 3--查询
select top 1 * from(
select * from ( select top 1 id from @t1 order by id desc) as a
union all
select * from ( select top 1 id from @t2 order by id desc ) as b
)a order by id desc--结果:
id
-----------
3(所影响的行数为 1 行)
declare @t1 table(id int)
insert @t1 select 1
union all select 2declare @t2 table(id int)
insert @t2 select 1
union all select 3--查询
select * from(
select * from ( select top 1 id from @t1 order by id desc) as a
union all
select * from ( select top 1 id from @t2 order by id desc ) as b
)a order by id desc--结果
id
-----------
3
2(所影响的行数为 2 行)
1.除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
2.ORDER BY 子句可包含选择列表中没出现的项目,但是如果指定 SELECT DISTINCT 或 SELECT 语句包含 UNION 运算符,则排序列必须出现在选择列表中。
3.当 SELECT 语句包含 UNION 运算符时,列名或列的别名必须是在第一选择列表内指定的列名或列的别名。
那为知道他们的区别?
union 在结果集中删除重复行。