不知道你是要做什么,是不是要这样?
select * from
(select top 100 * from A where ...
Union
Select top 100 * from B where ...) t1
order by field1
select * from
(select top 100 * from A where ...
Union
Select top 100 * from B where ...) t1
order by field1
select top 100 * from A where ... order by field1
Union
(Select top 100 * from B where ... order by field1)
即先取得A表排序后的前100条记录,再取得B表排序后的前100条记录,最后将两次的结果集合并后返回,也就是返回200条记录
select top 100 * from A where ... order by field1
Union
(Select top 100 * from B where ... order by field1)
的效果,但由于此语句不通,不知应如何写
是不是排序方式搞错了?order by field1 desc --降序
(select top 100 * from A where ... order by field1
Union all
Select top 100 * from B where ... order by field1)t1
order by field1
(select top 100 * from A where ... order by field1
Union all
Select top 100 * from B where ... order by field1)t1
order by field1通不过,因为select top 100 * from A where ... order by field1和Select top 100 * from B where ... order by field1中都有Order by,但用了Union之后,是禁止这样使用的谢谢楼上,我用的是SQL 2000
create table A(id int ,name varchar(10))
create table B(id int ,name varchar(10))
insert A values(1,'123')
insert A values(2,'456')
insert A values(3,'789')
insert B values(1,'123')
insert B values(2,'456')
insert B values(3,'789')
=================================================
select * from
(select top 100 ID,NAME from A order by ID
Union all
Select top 100 ID,NAME from B order by ID)t1
order by ID
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)ID NAME
----------- ----------
1 123
1 123
2 456
2 456
3 789
3 789(所影响的行数为 6 行)=========================================================把 “*” 改成 列名
create table A(id int ,name varchar(10))
create table B(id int ,name varchar(10))
insert A values(3,'989')
insert A values(1,'123')
insert A values(2,'456')
insert A values(3,'789')
insert B values(3,'689')
insert B values(1,'123')
insert B values(2,'456')
insert B values(3,'789')select * from
(select top 1 ID,NAME from A order by ID
Union all
Select top 1 ID,NAME from B order by ID)t1
order by ID执行结果:
ID NAME
----------- ----------
3 989
3 689
而不是:
ID NAME
----------- ----------
1 123
1 123
select top 3 * from test_b order by bexec ('declare @t table ( a int, b int)
insert @t select top 2 * from test_a order by b
insert @t select top 3 * from test_b order by b
select distinct * from @t')a b
----------- -----------
3 1
2 1(所影响的行数为 2 行)a b
----------- -----------
3 1
2 1
4 1(所影响的行数为 3 行)
(所影响的行数为 2 行)
(所影响的行数为 3 行)a b
----------- -----------
3 1
2 1
3 1
2 1
4 1
select top 3 * from test_b order by aexec ('declare @t table ( a int, b int)
insert @t select top 2 * from test_a order by a
insert @t select top 3 * from test_b order by a
select distinct * from @t')
a b
----------- -----------
1 1
2 1(所影响的行数为 2 行)a b
----------- -----------
2 1
3 1
4 1(所影响的行数为 3 行)
(所影响的行数为 2 行)
(所影响的行数为 3 行)a b
----------- -----------
1 1
2 1
3 1
4 1
select top 2 * from
(select top 1 ID,NAME from A
Union all
Select top 1 ID,NAME from B ) t1
order by ID
方法算了,用表变量反而会加大数据服务器的压力