Dear All:
想实现这样的查询,该如何实现呢?
有两个table Tb1,Tb2
Tb1
Custcode amt1 amt2 amt3
A 1 2 3
B 2 5 7Tb2
Custcode inv1 inv2 inv3 inv4
B 3 3 5 6
C 4 1 2 3
D 4 1 2 1如何实现这种查询
Custcode amt1 amt2 amt3 inv1 inv2 inv3 inv4
A 1 2 3 3 3 5 6
B 2 5 7
C 4 1 2 3
D 4 1 2 1
想实现这样的查询,该如何实现呢?
有两个table Tb1,Tb2
Tb1
Custcode amt1 amt2 amt3
A 1 2 3
B 2 5 7Tb2
Custcode inv1 inv2 inv3 inv4
B 3 3 5 6
C 4 1 2 3
D 4 1 2 1如何实现这种查询
Custcode amt1 amt2 amt3 inv1 inv2 inv3 inv4
A 1 2 3 3 3 5 6
B 2 5 7
C 4 1 2 3
D 4 1 2 1
解决方案 »
- 统计库存如何可以合并以下两条SQL语句?
- select '\'' escapte '\' 这样为何不行
- 为什么这样的select赋值不行?
- SQL语句问题,请大家指教
- 关于创建聚簇索引的问题
- c# 中excel数据导入问题
- 一个用 sql转列问题
- 多条件查询代码,不会写,请教大家!!!
- MSSQL和SQL SERVER的区别
- 请教高手,怎样在sql server 2000中存入图像文件,在sql server 2000中应如何处理?
- 关于用sql语句打开excel的问题
- 关于decimal浮点数的问题,我想把一个计算过程保存起来,直接print 出来的结果是正确的,为什么转换一下就错了?谢谢!
IsNull(A.Custcode,B.Custcode) As Custcode,
amt1,
amt2,
amt3,
inv1,
inv2,
inv3,
inv4
From Tb1 A
Full Join Tb2 B
On A.Custcode=B.Custcode
isnull(a.Custcode,b.Custcode) as Custcode,
a.amt1,
a.amt2,
a.amt3,
a.inv1,
a.inv2,
a.inv3,
a.inv4
from
Tb1
full outer join
Tb2
on
a.Custcode=b.Custcode
order by
a.Custcode
A 1 2 3
B 2 5 7 3 3 5 6
C 4 1 2 3
D 4 1 2 1
select
Custcode = (Case when a.Custcode is null then b.Custcode else a.Custcode End)
,amt1 ,amt2 ,amt3 ,inv1 ,inv2, inv3 ,inv4
from tb1 a
full join tb2 b
on a.Custcode = b.Custcode
(Custcode Char(1),
amt1 Int,
amt2 Int,
amt3 Int)
Insert Tb1 Select 'A', 1, 2, 3
Union All Select 'B', 2, 5, 7Create Table Tb2
(Custcode Char(1),
inv1 Int,
inv2 Int,
inv3 Int,
inv4 Int)
Insert Tb2 Select 'B', 3, 3, 5, 6
Union All Select 'C', 4, 1, 2, 3
Union All Select 'D', 4, 1, 2, 1
GO
Select
IsNull(A.Custcode,B.Custcode) As Custcode,
amt1,
amt2,
amt3,
inv1,
inv2,
inv3,
inv4
From Tb1 A
Full Join Tb2 B
On A.Custcode=B.Custcode
Order By Custcode
GO
Drop Table Tb1,Tb2
/*
Custcode amt1 amt2 amt3 inv1 inv2 inv3 inv4
A 1 2 3 NULL NULL NULL NULL
B 2 5 7 3 3 5 6
C NULL NULL NULL 4 1 2 3
D NULL NULL NULL 4 1 2 1
*/
Custcode amt1 amt2 amt3 inv1 inv2 inv3 inv4
A 1 2 3
B 2 5 7 3 3 5 6
C 4 1 2 3
D 4 1 2 1我来试一下大家给的方法
(Custcode Char(1),
amt1 Int,
amt2 Int,
amt3 Int)
Insert @t1 Select 'A', 1, 2, 3
Union All Select 'B', 2, 5, 7declare @t2 Table
(Custcode Char(1),
inv1 Int,
inv2 Int,
inv3 Int,
inv4 Int)
Insert @t2 Select 'B', 3, 3, 5, 6
Union All Select 'C', 4, 1, 2, 3
Union All Select 'D', 4, 1, 2, 1------------
select
isnull(cast(a.Custcode as varchar(5)),b.Custcode)as Custcode,
isnull(cast(a.amt1 as varchar(5)),'') as amt1,
isnull(cast(a.amt2 as varchar(5)),'')as amt2,
isnull(cast(a.amt3 as varchar(5)),'')as amt3,
isnull(cast(b.inv1 as varchar(5)),'')as inv1,
isnull(cast(b.inv2 as varchar(5)),'')as inv2,
isnull(cast(b.inv3 as varchar(5)),'')as inv3,
isnull(cast(b.inv4 as varchar(5)),'')as inv4
from @t1 a
left join @t2 b
on
a.Custcode = b.Custcodeunionselect
isnull(cast(a.Custcode as varchar(5)),b.Custcode),
isnull(cast(a.amt1 as varchar(5)),'') as amt1,
isnull(cast(a.amt2 as varchar(5)),'')as amt2,
isnull(cast(a.amt3 as varchar(5)),'')as amt3,
isnull(cast(b.inv1 as varchar(5)),'')as inv1,
isnull(cast(b.inv2 as varchar(5)),'')as inv2,
isnull(cast(b.inv3 as varchar(5)),'')as inv3,
isnull(cast(b.inv4 as varchar(5)),'')as inv4
from @t1 a
right join @t2 b
on
a.Custcode = b.Custcode
-- A 1 2 3
-- B 2 5 7 3 3 5 6
-- C 4 1 2 3
-- D 4 1 2 1