select distinct * from( select * from A union all select * from B)T
select * from tb1 union select * from tb2 order by NO --加个排序
--也可以用full join ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-03-17 09:59:05 -- Verstion: -- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) -- Dec 16 2008 19:46:53 -- Copyright (c) 1988-2003 Microsoft Corporation -- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([No] int,[Name] int) insert [a] select 1,1 union all select 2,2 --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([No] int,[Name] int) insert [b] select 2,2 union all select 3,3 --------------开始查询-------------------------- select isnull(a.no,b.no) as [no],isnull(a.name,b.name) as name from [a] full join b on a.no=b.no ----------------结果---------------------------- /* no name ----------- ----------- 1 1 2 2 3 3(所影响的行数为 3 行)*/
SELECT No, Name FROM 表1 UNION SELECT No, Name FROM 表2
莫非: --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([No] int,[Name] int) insert [a] select 1,1 union all select 2,2 union all select 4,4--> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([No] int,[Name] int) insert [b] select 2,2 union all select 3,3 union all select 5,5 --------------开始查询-------------------------- select no,name from ( select no,name from [a] union all select no,name from [b] )as t group by no,name drop table [a] drop table [b]
union
select * from B是这个意思吧?
union
select * from tb2
select * from A
union all
select * from B)T
select * from tb1
union
select * from tb2
order by NO
--加个排序
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-17 09:59:05
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([No] int,[Name] int)
insert [a]
select 1,1 union all
select 2,2
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([No] int,[Name] int)
insert [b]
select 2,2 union all
select 3,3
--------------开始查询--------------------------
select
isnull(a.no,b.no) as [no],isnull(a.name,b.name) as name
from
[a] full join b
on
a.no=b.no
----------------结果----------------------------
/* no name
----------- -----------
1 1
2 2
3 3(所影响的行数为 3 行)*/
SELECT No, Name FROM 表1
UNION
SELECT No, Name FROM 表2
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([No] int,[Name] int)
insert [a]
select 1,1 union all
select 2,2 union all
select 4,4--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([No] int,[Name] int)
insert [b]
select 2,2 union all
select 3,3 union all
select 5,5
--------------开始查询--------------------------
select no,name
from
(
select no,name from [a]
union all
select no,name from [b]
)as t
group by no,name
drop table [a]
drop table [b]
union
select * from tb2