Table A
order ln orderqt
co01 1 1000
co01 2 2000
co01 3 2000
co02 1 500Table B order ln date isqt
co01 1 2011-02-01 200
co01 1 2011-02-15 500
co01 2 2011-02-05 300
co01 3 2011-02-28 500我想得到: table corder ln lstdate
co01 1 2011-02-15
co01 3 2011-02-28
co01 2 空白
co02 1 空白
order ln orderqt
co01 1 1000
co01 2 2000
co01 3 2000
co02 1 500Table B order ln date isqt
co01 1 2011-02-01 200
co01 1 2011-02-15 500
co01 2 2011-02-05 300
co01 3 2011-02-28 500我想得到: table corder ln lstdate
co01 1 2011-02-15
co01 3 2011-02-28
co01 2 空白
co02 1 空白
a.[in],
b.[date]
from ta a left join tb b on a.[order]=b.[order] and a.[in]=b.[id]
where not exists(select 1
from tb
where [order]=b.[order] and [in]=b.[id] and [date]>b.[date])
drop table ta
Go
Create table ta([order] nvarchar(4),[ln] int,[orderqt] int)
Insert ta
select N'co01',1,1000 union all
select N'co01',2,2000 union all
select N'co01',3,2000 union all
select N'co02',1,500
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([order] nvarchar(4),[ln] int,[date] Datetime,[isqt] int)
Insert tb
select N'co01',1,'2011-02-01',200 union all
select N'co01',1,'2011-02-15',500 union all
select N'co01',2,'2011-02-05',300 union all
select N'co01',3,'2011-02-28',500
Go
select a.[order],
a.[ln],
b.[date]
from ta a left join tb b on a.[order]=b.[order] and a.[ln]=b.[ln]
where not exists(select 1
from tb
where [order]=b.[order] and [ln]=b.[ln] and [date]>b.[date])
/*
order ln date
----- ----------- -----------------------
co01 1 2011-02-15 00:00:00.000
co01 2 2011-02-05 00:00:00.000
co01 3 2011-02-28 00:00:00.000
co02 1 NULL*/
--貌似结果有出入
if not object_id('ta') is null
drop table ta
Go
Create table ta([order] nvarchar(4),[ln] int,[orderqt] int)
Insert ta
select N'co01',1,1000 union all
select N'co01',2,2000 union all
select N'co01',3,2000 union all
select N'co02',1,500
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([order] nvarchar(4),[ln] int,[date] Datetime,[isqt] int)
Insert tb
select N'co01',1,'2011-02-01',200 union all
select N'co01',1,'2011-02-15',500 union all
select N'co01',2,'2011-02-05',300 union all
select N'co01',3,'2011-02-28',500
Goselect *
from (
select [order],[ln]
from ta
group by [order],[ln]
)A
outer apply(
select lstdate = MAX([date])
from tb
where [order] = A.[order]
and [ln] = A.[ln]
)B
a.[order],
a.[ln],
b.[date]
from
ta a
left join
tb b
on
a.[order]=b.[order]
and
a.[ln]=b.[ln]
where
[date]=(select max([date])from tb where [order]=b.[order] and [ln]=b.[ln])
a.[ln],
b.[date]
from ta a left join tb b on a.[order]=b.[order] and a.[ln]=b.[ln]
where not exists(select 1
from tb
where [order]=b.[order] and [ln]=b.[ln] and [date]>b.[date])
select a.[order],
a.[in],
b.[date]
from ta a left join
(select [order],[ln],max(date) date from tb group by [order],[ln]) b
on a.[order] = b.[order] and a.[ln] = b.[ln]
from
ta a left join tb b
on a.[order]=b.[order]
and a.[ln]=b.[ln]
where [date]=(select max([date])from tb where [order]=b.[order] and [ln]=b.[ln])