34,590
社区成员
发帖
与我相关
我的任务
分享
create table A (AID int,BID int,M varchar(10))
insert into A
select 1,2,'X' union all
select 2,2,'S' union all
select 3,1,'P'
create table B (BID int,SupplierName varchar(3))
insert into B
select 1,'IBM' union all
select 2,'MS'
create table C (CID int,AID int,Memo varchar(10),FDate datetime,M varchar(10))
insert into C
select 1,1,'OK','08-03-02','X' union all
select 2,1,'NO','08-03-01','X' union all
select 3,1,'Cancel','08-01-02','X' union all
select 4,2,'Bad','08-03-02','S'
select a.*,b.SupplierName,c.Memo,c.Fdate from a left join b on a.aid=b.bid
left join (select * from c t where not exists(select 1 from c where aid=t.aid and fdate>t.fdate))c
on a.aid=c.aid and a.m=c.m
--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID int,SupplierID int,M int)
insert into #A
select 1,2,1 union all
select 2,2,11 union all
select 3,1,2
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (ID int,SupplierName varchar(3))
insert into #B
select 1,'IBM' union all
select 2,'MS'
--> 测试数据: #C
if object_id('tempdb.dbo.#C') is not null drop table #C
create table #C (ID int,AID int,V int,FDate datetime)
insert into #C
select 1,1,32,'08-03-02' union all
select 2,1,24,'08-03-01' union all
select 3,1,13,'08-01-02' union all
select 4,2,34,'08-03-02'
select a.*,b.SupplierName,c.V,c.FDate
from #A a left join #B b on a.ID=b.ID left join #C c on a.ID=c.AID
where C.AID is null or c.FDate=(select max(FDate) from #C where AID=c.AID)
/*
ID SupplierID M SupplierName V FDate
----------- ----------- ----------- ------------ ----------- -----------------------
1 2 1 IBM 32 2002-08-03 00:00:00.000
2 2 11 MS 34 2002-08-03 00:00:00.000
3 1 2 NULL NULL NULL
(3 row(s) affected)
*/
drop table #A, #B, #C
select a.*, b.SupplierName, c.V, c.FDate
from A a left join B b on A.SupplierID=B.ID
left join (select AID, max(FDate) as date from c group by AID) c on a.ID=c.AID