create table QUALITY_MOUNT_A(SERIAL int,IDNUM int,PARTSNAME varchar(10),B int,C varchar(10)) insert QUALITY_MOUNT_A select 1,1,'NAME1',1,'abc' union all select 1,1,'NAME1',1,'abc' union all select 1,2,'NAME2',3,'abc' union all select 1,3,'NAME3',5,'abc' union all select 1,4,'NAME4',5,'abc' union all select 1,5,'NAME5',5,'abc' union all select 1,6,'NAME6',5,'def' union all select 1,7,'NAME7',9,'abc'create table QUALITY_SLD_B(SERIAL int,IDNUM int,SIDNUM int,INS_RESULT int) insert QUALITY_SLD_B select 1,3,1,99 union all select 1,3,1,99 union all select 1,3,2,100 union all select 1,5,2,1 union all select 1,5,3,8 union all select 1,5,6,6 union all select 1,6,1,1 union all select 1,7,1,5 ;with t1 as (select id=row_number() over(partition by SERIAL,IDNUM order by (select 1)),* from QUALITY_SLD_B) ,t2 as (select * from t1 tb where id=(select min(id) from t1 where SERIAL=tb.SERIAL and IDNUM=tb.IDNUM)) select t3.*,t2.SERIAL,t2.IDNUM,t2.SIDNUM,t2.INS_RESULT from QUALITY_MOUNT_A t3 left join t2 on t3.SERIAL=t2.SERIAL and t3.IDNUM=t2.IDNUM where t3.B=5 and t3.C='abc'SERIAL IDNUM PARTSNAME B C SERIAL IDNUM SIDNUM INS_RESULT ----------- ----------- ---------- ----------- ---------- ----------- ----------- ----------- ----------- 1 3 NAME3 5 abc 1 3 1 99 1 4 NAME4 5 abc NULL NULL NULL NULL 1 5 NAME5 5 abc 1 5 2 1
create table QUALITY_MOUNT_A(SERIAL int,IDNUM int,PARTSNAME varchar(10),B int,C varchar(10))
insert QUALITY_MOUNT_A select 1,1,'NAME1',1,'abc'
union all select 1,1,'NAME1',1,'abc'
union all select 1,2,'NAME2',3,'abc'
union all select 1,3,'NAME3',5,'abc'
union all select 1,4,'NAME4',5,'abc'
union all select 1,5,'NAME5',5,'abc'
union all select 1,6,'NAME6',5,'def'
union all select 1,7,'NAME7',9,'abc'create table QUALITY_SLD_B(SERIAL int,IDNUM int,SIDNUM int,INS_RESULT int)
insert QUALITY_SLD_B select 1,3,1,99
union all select 1,3,1,99
union all select 1,3,2,100
union all select 1,5,2,1
union all select 1,5,3,8
union all select 1,5,6,6
union all select 1,6,1,1
union all select 1,7,1,5 ;with t1 as
(select id=row_number() over(partition by SERIAL,IDNUM order by (select 1)),* from QUALITY_SLD_B)
,t2 as
(select * from t1 tb where id=(select min(id) from t1 where SERIAL=tb.SERIAL and IDNUM=tb.IDNUM))
select t3.*,t2.SERIAL,t2.IDNUM,t2.SIDNUM,t2.INS_RESULT from QUALITY_MOUNT_A t3 left join t2 on t3.SERIAL=t2.SERIAL and t3.IDNUM=t2.IDNUM
where t3.B=5 and t3.C='abc'SERIAL IDNUM PARTSNAME B C SERIAL IDNUM SIDNUM INS_RESULT
----------- ----------- ---------- ----------- ---------- ----------- ----------- ----------- -----------
1 3 NAME3 5 abc 1 3 1 99
1 4 NAME4 5 abc NULL NULL NULL NULL
1 5 NAME5 5 abc 1 5 2 1