34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('A') is not null
drop table A
if object_id('B') is not null
drop table B
create table A (bm int, lh varchar(20), sl int,dj int,hth int, rq smalldatetime)
insert A (bm, lh, sl, dj, hth, rq )
select 1,'s123', 4 , 13, 12, '2009-3-14' union all
select 1,'s12', 4 , 13, 12, '2009-3-14' union all
select 1,'s122', 4 , 13, 12, '2009-3-14' union all
select 1,'s123', 4 , 13, 12, '2009-3-14'
create table B (bm int, lh varchar(20), sl int,dj int, rq smalldatetime)
insert into B (bm, lh, sl, dj, rq )
select 1,'s123', 1 , 13, '2009-3-14'
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM A
SELECT T.lh, T.sl, T.dj ,T.hth, T.rq,T1.lh ,T1.sl,T1.dj ,T1.rq
FROM #TB T LEFT JOIN B T1
ON T.bm=T1.bm
AND T.ID=4
s123 4 13 12 2009-03-14 00:00:00 NULL NULL NULL NULL
s12 4 13 12 2009-03-14 00:00:00 NULL NULL NULL NULL
s122 4 13 12 2009-03-14 00:00:00 NULL NULL NULL NULL
s123 4 13 12 2009-03-14 00:00:00 s123 1 13 2009-03-14 00:00:00
if object_id('[a]') is not null drop table [a]
go
create table [a]([编码] int,[令号] varchar(10),[数量] int,[单价] int,[合同号] int,[日期] varchar(10))
insert [a] select 1,'s123',4,13,12,'2009-3-14'
union all select 1,'s12',4,13,12,'2009-3-14'
union all select 1,'s122',4,13,12,'2009-3-14'
union all select 1,'s123',4,13,12,'2009-3-14'
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([编码] int,[令号] varchar(10),[数量] int,[单价] int,[日期] varchar(10))
insert [b] select 1,'s123',1,13,'2009-3-14'
go
--临时表处理法:
select id=identity(int),* into #a from a
select id=identity(int),* into #b from b
select a.编码,a.令号,a.数量,a.单价,a.合同号,a.日期,b.编码,b.令号,b.数量,b.单价,b.日期
from (select px=(select count(1) from #a where 编码=t.编码 and id<=t.id),* from #a t) a
full join (select px=(select count(1) from #b where 编码=t.编码 and id<=t.id),* from #b t) b
on a.px=b.px
/*
编码 令号 数量 单价 合同号 日期 编码 令号 数量 单价 日期
----------- ---------- ----------- ----------- ----------- ---------- ----------- ---------- ----------- ----------- ----------
1 s123 4 13 12 2009-3-14 1 s123 1 13 2009-3-14
1 s12 4 13 12 2009-3-14 NULL NULL NULL NULL NULL
1 s122 4 13 12 2009-3-14 NULL NULL NULL NULL NULL
1 s123 4 13 12 2009-3-14 NULL NULL NULL NULL NULL
(4 行受影响)
*/
drop table #a,#b
/×结果
1 s123 4 13 12 2009-03-14 00:00:00 s123 1 13 2009-03-14 00:00:00
1 s12 4 13 12 2009-03-14 00:00:00 NULL NULL NULL NULL
1 s122 4 13 12 2009-03-14 00:00:00 NULL NULL NULL NULL
1 s123 4 13 12 2009-03-14 00:00:00 s123 1 13 2009-03-14 00:00:00
×/
if object_id('A') is not null
drop table A
if object_id('B') is not null
drop table B
create table A (bm int, lh varchar(20), sl int,dj int,hth int, rq smalldatetime)
insert A (bm, lh, sl, dj, hth, rq )
select 1,'s123', 4 , 13, 12, '2009-3-14' union all
select 1,'s12', 4 , 13, 12, '2009-3-14' union all
select 1,'s122', 4 , 13, 12, '2009-3-14' union all
select 1,'s123', 4 , 13, 12, '2009-3-14'
create table B (bm int, lh varchar(20), sl int,dj int, rq smalldatetime)
insert into B (bm, lh, sl, dj, rq )
select 1,'s123', 1 , 13, '2009-3-14'
--结果
SELECT A.*,B.lh,B.sl,B.dj,B.rq
FROM A LEFT JOIN B
ON A.lh = B.lh
if object_id('[a]') is not null drop table [a]
go
create table [a]([编码] int,[令号] varchar(10),[数量] int,[单价] int,[合同号] int,[日期] varchar(10))
insert [a] select 1,'s123',4,13,12,'2009-3-14'
union all select 1,'s12',4,13,12,'2009-3-14'
union all select 1,'s122',4,13,12,'2009-3-14'
union all select 1,'s123',4,13,12,'2009-3-14'
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([编码] int,[令号] varchar(10),[数量] int,[单价] int,[日期] varchar(10))
insert [b] select 1,'s123',1,13,'2009-3-14'
go
select a.*,b.*
from a
left join b
on a.编码=a.编码 and a.令号=b.令号
/*
编码 令号 数量 单价 合同号 日期 编码 令号 数量 单价 日期
----------- ---------- ----------- ----------- ----------- ---------- ----------- ---------- ----------- ----------- ----------
1 s123 4 13 12 2009-3-14 1 s123 1 13 2009-3-14
1 s12 4 13 12 2009-3-14 NULL NULL NULL NULL NULL
1 s122 4 13 12 2009-3-14 NULL NULL NULL NULL NULL
1 s123 4 13 12 2009-3-14 1 s123 1 13 2009-3-14
(4 行受影响)
*/
SELECT A.*,B.令号,B.数量,B.单价,B.日期
FROM A LEFT JOIN B
ON A.令号 = B.令号
SELECT ID=IDENTITY(INT,1,1),* INTO #TB FROM A
SELECT T.令号, T.数量, T.单价 ,T.合同号, T.日期,T1.令号 ,T1.数量,T1. 单价 ,T1.日期
FROM #TB T LEFT JOIN B T1
ON T.编码=T1.编码
AND T.ID=4