mdno seq qty
m01 1 100
m01 2 200
m02 1 200
m02 2 350mdkg
mdno mkqty inqty
m01 300 200
m02 550 350要求得出第三第表,结果如下:
mdno seq qty mkqty inqty
m01 1 100 300 200
m01 2 200
m02 1 200 550 350
m02 2 350
m01 1 100
m01 2 200
m02 1 200
m02 2 350mdkg
mdno mkqty inqty
m01 300 200
m02 550 350要求得出第三第表,结果如下:
mdno seq qty mkqty inqty
m01 1 100 300 200
m01 2 200
m02 1 200 550 350
m02 2 350
ta.*
isnull(tb.mkqty,0),
isnull(tb.inqty,0)
from ta
left join tb on ta.mdno=tb.mdno
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([mdno] varchar(3),[seq] int,[qty] int)
insert [ta]
select 'm01',1,100 union all
select 'm01',2,200 union all
select 'm02',1,200 union all
select 'm02',2,350
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([mdno] varchar(3),[mkqty] int,[inqty] int)
insert [tb]
select 'm01',300,200 union all
select 'm02',550,350
---查询---
select
ta.*,
isnull(tb.mkqty,0) as mkqty,
isnull(tb.inqty,0) as inqty
from ta
left join tb on ta.mdno=tb.mdno and ta.seq=1---结果---
mdno seq qty mkqty inqty
---- ----------- ----------- ----------- -----------
m01 1 100 300 200
m01 2 200 0 0
m02 1 200 550 350
m02 2 350 0 0(所影响的行数为 4 行)
但是第一张表的起始流水不一定是1,也许是2开始也许是3,
---测试数据---
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([mdno] varchar(3),[seq] int,[qty] int)
insert [ta]
select 'm01',1,100 union all
select 'm01',2,200 union all
select 'm02',3,200 union all
select 'm02',2,350
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([mdno] varchar(3),[mkqty] int,[inqty] int)
insert [tb]
select 'm01',300,200 union all
select 'm02',550,350
---查询---
select
t.*,
isnull(tb.mkqty,0) as mkqty,
isnull(tb.inqty,0) as inqty
from ta t
left join tb on t.mdno=tb.mdno
where t.seq in(select min(seq) from ta where mdno=t.mdno)
union all
select t.*,0,0
from ta t
where t.seq not in(select min(seq) from ta where mdno=t.mdno)
order by t.mdno,t.seq---结果---
mdno seq qty mkqty inqty
---- ----------- ----------- ----------- -----------
m01 1 100 300 200
m01 2 200 0 0
m02 2 350 550 350
m02 3 200 0 0(所影响的行数为 4 行)