T-sql中如何将多个查询结果合并为一张表

Question

select top 8 id from rad where radzm='aa' order by radmc desc
select top 8 id from rad where radzm='bb' order by radmc desc
select top 8 id from rad where radzm='cc' order by radmc desc
select top 8 id from rad where radzm='dd' order by radmc desc
现在有这样的4个查询结果，如何将这4个查询结果合并到一个临时表中？

Answer 1

Use union to put them together:

select top 8 id from rad where radzm='aa' order by radmc desc
union all
select top 8 id from rad where radzm='bb' order by radmc desc
union all
select top 8 id from rad where radzm='cc' order by radmc desc
union all
select top 8 id from rad where radzm='dd' order by radmc desc

Answer 2

select top 8 id from rad where radzm='aa' order by radmc desc
 union all
select top 8 id from rad where radzm='bb' order by radmc desc
 union all
select top 8 id from rad where radzm='cc' order by radmc desc
 union all
select top 8 id from rad where radzm='dd' order by radmc desc

Answer 3

select top 8 id from rad where radzm='aa' order by radmc desc
union all
select top 8 id from rad where radzm='bb' order by radmc desc
union all
select top 8 id from rad where radzm='cc' order by radmc desc
union all
select top 8 id from rad where radzm='dd' order by radmc desc
这样写在怎么在查询分析器中说
服务器: 消息 156，级别 15，状态 1，行 2
在关键字 'union' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 4
在关键字 'union' 附近有语法错误。
服务器: 消息 156，级别 15，状态 1，行 6
在关键字 'union' 附近有语法错误。

Answer 4

select id
from 
(
	select id,RANK() over (PARTITION BY radzm order by  radmc desc) as r 
	from rad
	where radzm in ('aa','bb','cc','dd')
) A
where A.r<=8

SQL2005以上，radmc没有重复值的话(有重复值可能会比8条多)，可以考虑用rank

Answer 5

谢谢你们。

Answer 6

create table #tmp(id int)
insert into  #tmp     
select top 8 id from rad where radzm='aa' order by radmc desc

insert into  #tmp     
select top 8 id from rad where radzm='bb' order by radmc desc

insert into  #tmp     
select top 8 id from rad where radzm='cc' order by radmc desc

insert into  #tmp     
select top 8 id from rad where radzm='dd' order by radmc desc

select * from #tmp

drop table #tmp

其实你分多次insert不是也可以的吗

Answer 7

那这两种相比之下那个的效率会高呢?

Answer 8

select * from (select top 8 id from rad where radzm='aa' order by radmc desc)aa
 union all
select * from (select top 8 id from rad where radzm='bb' order by radmc desc)aa
 union all
select * from (select top 8 id from rad where radzm='cc' order by radmc desc)aa
 union all
select * from (select top 8 id from rad where radzm='dd' order by radmc desc)aa

Answer 9

恩，我最后也改成你这个样子的了。

Answer 10

select id from
(
 select *,RowIndex=row_number() over(partition by radzm order by radmc desc)
 from rad
 where radzm in('aa','bb','cc','dd')
)Temp where Temp.RowIndex<=8

这个至少2005或以上版本才支持

Answer 11

select rad1.id from rad rad1
cross apply
(
	select top 8 * from rad rad2 where rad1.radzm=rad2.radzm
	where rad2.radzm in('aa','bb','cc','dd')
	order by rad2.radmc desc
)rad3
where rad1.id=rad3.id