表A ID1,name1,gz1
1, aa, 10
2, bb, 20
3, cc, 20
表B ID2,name2,gz2
1, aa,, 5
2, bb, 2
4, dd, 4
5, ee, 1希望查询的结果为:id , name,gz1,gz2
1, aa, 10,5
2, bb, 20,2
3, cc, 20,0
4, dd, 0, 4
5, ee, 0, 1
1, aa, 10
2, bb, 20
3, cc, 20
表B ID2,name2,gz2
1, aa,, 5
2, bb, 2
4, dd, 4
5, ee, 1希望查询的结果为:id , name,gz1,gz2
1, aa, 10,5
2, bb, 20,2
3, cc, 20,0
4, dd, 0, 4
5, ee, 0, 1
解决方案 »
- 求解:win7 (windows 7)下SQL Server管理器无法连接到远程SQL数据库
- select 1 from 后,返回的是什么呢?
- login name是"admin",数据库对应的User怎么会变成"dbo"了,而且这个用户删除不了!!
- sql数据库字段记录局部替换
- 大家看看这条语句怎解释?
- 求救 !!!邹健等大侠能来帮个忙吗?
- 请问用mumeric怎样显示小数点前的"0"?
- 再次请教SQL order by 字符排序
- 很菜的问题,急待解决,sos!!!
- 怎么查询第多少列
- compute by 子句一般用在哪些场合呀。返回的是两个结果集,而且列数也不同呀?
- 关于SQL创建表的一些实例
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-10 17:16:00
---------------------------------
--> 生成测试数据表:aIf not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([ID1] int,[name1] nvarchar(2),[gz1] int)
Insert a
Select 1,'aa',10 union all
Select 2,'bb',20 union all
Select 3,'cc',20
Go
--Select * from a--> 生成测试数据表:bIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([ID2] int,[name2] nvarchar(2),[gz2] int)
Insert b
Select 1,'aa',5 union all
Select 2,'bb',2 union all
Select 4,'dd',4 union all
Select 5,'ee',1
Go
--Select * from b-->SQL查询如下:select isnull(a.id1,b.id2) ID,isnull(a.name1,b.name2) name,isnull(a.gz1,0) gz1,isnull(b.gz2,0) gz2
from a
full join b
on a.id1=b.id2
/*
ID name gz1 gz2
----------- ---- ----------- -----------
1 aa 10 5
2 bb 20 2
3 cc 20 0
4 dd 0 4
5 ee 0 1(5 行受影响)
*/
--> 我的淘宝:http://shop36766744.taobao.com/if object_id('[表A]') is not null drop table [表A]
create table [表A]([ID1] int,[name1] varchar(2),[gz1] int)
insert [表A]
select 1,'aa',10 union all
select 2,'bb',20 union all
select 3,'cc',20if object_id('[表B]') is not null drop table [表B]
create table [表B]([ID2] int,[name2] varchar(2),[gz2] int)
insert [表B]
select 1,'aa',5 union all
select 2,'bb',2 union all
select 4,'dd',4 union all
select 5,'ee',1select ID=isnull(ID2,ID1),
name=isnull(name1,name2),
gz1=isnull(gz1,0),
gz2=isnull(gz2,0)
from [表A] A full join [表B] B on ID1=ID2
order by ID/*ID name gz1 gz2
----------- ---- ----------- -----------
1 aa 10 5
2 bb 20 2
3 cc 20 0
4 dd 0 4
5 ee 0 1(所影响的行数为 5 行)*/
select A.*,B.*
from A,full join B
on A.id=B.id
full join b on a.id1=b.id2 and a. name1=b.name2
(
ID1 int,
name1 nvarchar(30),
gz1 int
)
create table #B
(
ID2 int,
name2 nvarchar(30),
gz2 int
)
insert into #A
select
1, 'aa', 10 union all
select
2, 'bb', 20 union all
select
3, 'cc', 20
insert into #B
select
1, 'aa', 5 union all
select
2, 'bb', 2 union all
select
4, 'dd', 4 union all
select
5, 'ee', 1
select ISNULL(ID1,ID2) ID,
ISNULL(name1,name2) name,
ISNULL(gz1,0) gz1,
ISNULL(gz2,0) gz2
from #A full join #B on #A.ID1=#B.ID2
drop table #A
drop table #B
union select T_B.ID2 as ID,T_B.Name2 as Name,isnull(T_A.Gz1,0) as gz1,isnull(T_B.gz2,0) as gz2 from T_A right join T_B on T_A.ID1=T_B.ID2
If not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([ID1] int,[name1] nvarchar(2),[gz1] int)
Insert a
Select 1,'aa',10 union all
Select 2,'bb',20 union all
Select 3,'cc',20
Go
--Select * from aIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([ID2] int,[name2] nvarchar(2),[gz2] int)
Insert b
Select 1,'aa',5 union all
Select 2,'bb',2 union all
Select 4,'dd',4 union all
Select 5,'ee',1
Goselect a.ID1 as ID,A.name1 AS NAME,gz1,GZ2 from a join b on a.ID1=b.ID2
UNION ALL
SELECT ID1,NAME1,GZ1,GZ2=0 FROM a WHERE ID1 NOT IN(SELECT a.ID1 from a join b on a.ID1=b.ID2)
UNION ALL
SELECT ID2,NAME2,GZ1=0,GZ2 FROM B WHERE ID2 NOT IN(SELECT a.ID1 from a join b on a.ID1=b.ID2)
ID NAME gz1 GZ2
----------- ---- ----------- -----------
1 aa 10 5
2 bb 20 2
3 cc 20 0
4 dd 0 4
5 ee 0 1
FROM A LEFT OUTER JOIN B ON A.ID1 = B.ID2
UNION
SELECT B.ID2, B.name2, ISNULL(A.gz1,0), ISNULL(B.gz2,0)
FROM A RIGHT OUTER JOIN B ON A.ID1 = B.ID2