select * from A表 a left join(select 手动编号,金额,时间=Max(时间)from B表 group by 手动编号,金额)b on a.手动编号=b.手动编号 漏了一个a,呵呵...
--> Test Data: @ta declare @ta table ([ID] int,[手动编号] varchar(5),[姓名] varchar(2)) insert into @ta select 1,'J0001','张' union all select 2,'J0002','王' union all select 3,'J0003','李' --> Test Data: @tb declare @tb table ([ID] int,[手动编号] varchar(5),[金额] int,[时间] datetime) insert into @tb select 1,'J0001',100,'2008-01-01' union all select 2,'J0001',200,'2008-01-02' union all select 3,'J0001',230,'2008-01-02' union all select 4,'J0002',100,'2008-01-01' union all select 5,'J0002',200,'2008-01-02'--select * from @ta --select * from @tb --Code select * from @ta a join (select * from @tb a where not exists(select 1 from @tb where [手动编号]=a.[手动编号] and ([时间]>a.[时间] or ([时间]=a.[时间] and ID>a.ID )) )) b on a.[手动编号]=b.[手动编号]--Result /* ID 手动编号 姓名 ID 手动编号 金额 时间 ----------- ----- ---- ----------- ----- ----------- ----------------------- 1 J0001 张 3 J0001 230 2008-01-02 00:00:00.000 2 J0002 王 5 J0002 200 2008-01-02 00:00:00.000 */
select a.* , o.* from a, ( select m.* from ( select t.* from b t where 时间 = (select max(时间) from b where 手动编号 = t.手动编号) ) m where ID = (select max(id) from ( select t.* from b t where 时间 = (select max(时间) from b where 手动编号 = t.手动编号) ) n where 手动编号 = n.手动编号 ) ) o where a.手动编号 = o.手动编号
--Code select a.*,b.ID,b.手动编号,b.金额,b.时间 from @ta a join (select * from (select px=ROW_NUMBER() over(PARTITION by [手动编号] order by [时间] desc,[ID] desc) ,* from @tb) a where px=1 ) b on a.[手动编号]=b.[手动编号] --Result /* ID 手动编号 姓名 ID 手动编号 金额 时间 ----------- ----- ---- ----------- ----- ----------- ----------------------- 1 J0001 张 3 J0001 230 2008-01-02 00:00:00.000 2 J0002 王 5 J0002 200 2008-01-02 00:00:00.000 */
on a.手动编号=b.手动编号
漏了一个a,呵呵...
declare @ta table ([ID] int,[手动编号] varchar(5),[姓名] varchar(2))
insert into @ta
select 1,'J0001','张' union all
select 2,'J0002','王' union all
select 3,'J0003','李'
--> Test Data: @tb
declare @tb table ([ID] int,[手动编号] varchar(5),[金额] int,[时间] datetime)
insert into @tb
select 1,'J0001',100,'2008-01-01' union all
select 2,'J0001',200,'2008-01-02' union all
select 3,'J0001',230,'2008-01-02' union all
select 4,'J0002',100,'2008-01-01' union all
select 5,'J0002',200,'2008-01-02'--select * from @ta
--select * from @tb
--Code
select * from @ta a
join
(select * from @tb a
where not exists(select 1 from @tb where [手动编号]=a.[手动编号]
and ([时间]>a.[时间] or ([时间]=a.[时间] and ID>a.ID ))
)) b
on a.[手动编号]=b.[手动编号]--Result
/*
ID 手动编号 姓名 ID 手动编号 金额 时间
----------- ----- ---- ----------- ----- ----------- -----------------------
1 J0001 张 3 J0001 230 2008-01-02 00:00:00.000
2 J0002 王 5 J0002 200 2008-01-02 00:00:00.000
*/
(
select m.* from
(
select t.* from b t where 时间 = (select max(时间) from b where 手动编号 = t.手动编号)
) m where ID = (select max(id) from
(
select t.* from b t where 时间 = (select max(时间) from b where 手动编号 = t.手动编号)
) n where 手动编号 = n.手动编号
)
) o
where a.手动编号 = o.手动编号
--Code
select a.*,b.ID,b.手动编号,b.金额,b.时间 from @ta a
join
(select * from
(select
px=ROW_NUMBER() over(PARTITION by [手动编号] order by [时间] desc,[ID] desc) ,* from @tb) a
where px=1
) b
on a.[手动编号]=b.[手动编号]
--Result
/*
ID 手动编号 姓名 ID 手动编号 金额 时间
----------- ----- ---- ----------- ----- ----------- -----------------------
1 J0001 张 3 J0001 230 2008-01-02 00:00:00.000
2 J0002 王 5 J0002 200 2008-01-02 00:00:00.000
*/