小鸟求sql 单表多类型

[phoebuswei]

问题是这样的
一个表有多类型，每个类型去出制定条记录
select top 1 name,.. from table where type=1
select top 1 name,.. from table where type=2
select top 1 name,.. from table where type=3
select top 1 name,.. from table where type=4
select top 3 name,.. from table where type=5
......
想怎么做能把它组合成一个结果集

[liangCK]

SELECT xx FROM xx
UNION ALL
SELECT xx FROM xx
UNION ALL
SELECT xx FROMxx

[phoebuswei]

UNION ALL
和UNION 有什么不一样吗
对查询的性能有影响吗

[phoebuswei]

关键字 'UNION' 附近有语法错误。

[phoebuswei]

是不是我写order by 的问题

select top 1 name,.. from table where type=1 order by n_date DESC,n_id DESC
UNION ALL

select top 1 name,.. from table where type=2 order by n_date DESC,n_id DESC
UNION ALL

select top 1 name,.. from table where type=3 order by n_date DESC,n_id DESC
UNION ALL

select top 1 name,.. from table where type=4 order by n_date DESC,n_id DESC
UNION ALL

select top 3 name,.. from table where type=5 order by n_date DESC,n_id DESC

[dawugui]

[Quote=引用 4 楼 phoebuswei 的回复:]
是不是我写order by 的问题

select top 1 name,.. from table where type=1 order by n_date DESC,n_id DESC
UNION ALL

select top 1 name,.. from table where type=2 order by n_date DESC,n_id DESC
UNION ALL

select top 1 name,.. from table where type=3 order by n_date DESC,n_id DESC
UNION ALL

select top 1 name,.. from table where type=4 order by n_date DESC,n_id DESC
UNION ALL

select top 3 name,.. from table where type=5 order by n_date DESC,n_id DESC
[/Quote]

select top 100 percent * from (select top 1 name,.. from table where type=1 order by n_date DESC,n_id DESC  ) t1
UNION ALL 
select top 100 percent * from (select top 1 name,.. from table where type=2 order by n_date DESC,n_id DESC  ) t2
UNION ALL 
select top 100 percent * from (select top 1 name,.. from table where type=3 order by n_date DESC,n_id DESC  ) t3
UNION ALL 
select top 100 percent * from (select top 1 name,.. from table where type=4 order by n_date DESC,n_id DESC  ) t4
UNION ALL 
select top 100 percent * from (select top 3 name,.. from table where type=5 order by n_date DESC,n_id DESC  ) t5

[肥龙上天]

[Quote=引用 2 楼 phoebuswei 的回复:]
UNION ALL
和UNION 有什么不一样吗
对查询的性能有影响吗
[/Quote]

UNION 会自动取出重复记录
UNION ALL 会保存所有记录
 UNION 因为要去除相同的数据，所以效率会稍微低一些

[肥龙上天]

select * from (select top 1 name,.. from table where type=1 order by n_date DESC,n_id DESC  ) t1
UNION ALL 
select * from (select top 1 name,.. from table where type=2 order by n_date DESC,n_id DESC  ) t2
UNION ALL 
select * from (select top 1 name,.. from table where type=3 order by n_date DESC,n_id DESC  ) t3
UNION ALL 
select * from (select top 1 name,.. from table where type=4 order by n_date DESC,n_id DESC  ) t4
UNION ALL 
select top 3 name,.. from table where type=5 order by n_date DESC,n_id DESC

[Roc_Lee]

select * from (select top 1 name,.. from table where type=1 order by n_date DESC,n_id DESC  ) A
UNION ALL 
select * from (select top 1 name,.. from table where type=2 order by n_date DESC,n_id DESC  ) B
UNION ALL 
select * from (select top 1 name,.. from table where type=3 order by n_date DESC,n_id DESC  ) C
UNION ALL 
select * from (select top 1 name,.. from table where type=4 order by n_date DESC,n_id DESC  ) D
UNION ALL 
select top 3 name,.. from table where type=5 order by n_date DESC,n_id DESC