积极答复者
T-sql中如何将多个查询结果合并为一张表

问题
-
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个查询结果合并到一个临时表中?
答案
-
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- 已建议为答案 Raymond Tang 2009年12月30日 3:32
- 取消建议作为答案 Aragn 2010年1月4日 1:30
- 已标记为答案 Aragn 2010年1月4日 1:30
-
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
- 已建议为答案 Raymond Tang 2009年12月30日 3:34
- 取消建议作为答案 Aragn 2010年1月4日 1:30
- 已标记为答案 Aragn 2010年1月4日 1:30
-
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不是也可以的吗- 已标记为答案 Aragn 2010年1月4日 1:30
-
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
- 已标记为答案 Aragn 2010年1月4日 1:30
全部回复
-
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- 已建议为答案 Raymond Tang 2009年12月30日 3:32
- 取消建议作为答案 Aragn 2010年1月4日 1:30
- 已标记为答案 Aragn 2010年1月4日 1:30
-
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
- 已建议为答案 Raymond Tang 2009年12月30日 3:34
- 取消建议作为答案 Aragn 2010年1月4日 1:30
- 已标记为答案 Aragn 2010年1月4日 1:30
-
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' 附近有语法错误。 -
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不是也可以的吗- 已标记为答案 Aragn 2010年1月4日 1:30
-
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
- 已标记为答案 Aragn 2010年1月4日 1:30
-
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或以上版本才支持- 已建议为答案 SQL STUDIO 2010年1月2日 3:41
-
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
- 已建议为答案 SQL STUDIO 2010年1月2日 3:41