表 A(caseId,caseNumber),表 B(caseId,handleTime)
B.caseId是A.caseId的外键.
对于A中每条记录,B中都有一条与之关联(可能两条或更多,只是handleTime(操作时间)不同而已)
例如A中某条记录:
1001 FS0010
B中有两条记录能与其关联:
1001 2008-08-12 15:00:00
1001 2008-08-13 16:00:00现在我要:
将A与B内连接起来,连接条件为: A.caseId=B.caseId。而且,对于A的每条记录,只关联B中handleTime最大的那条按上面的例子,则应该是这样:1001 FS0010 2008-08-13 16:00:00 (只关联16点的,不要15点的)How can I do that?
B.caseId是A.caseId的外键.
对于A中每条记录,B中都有一条与之关联(可能两条或更多,只是handleTime(操作时间)不同而已)
例如A中某条记录:
1001 FS0010
B中有两条记录能与其关联:
1001 2008-08-12 15:00:00
1001 2008-08-13 16:00:00现在我要:
将A与B内连接起来,连接条件为: A.caseId=B.caseId。而且,对于A的每条记录,只关联B中handleTime最大的那条按上面的例子,则应该是这样:1001 FS0010 2008-08-13 16:00:00 (只关联16点的,不要15点的)How can I do that?
FROM A
INNER JOIN B B1 ON A.CASEID=B1.CASEID
AND NOT EXISTS(SELECT 1 FROM B B2 WHERE B2.CASEID=B1.CASEID AND B2.HANDLETIME>B1.HANDLETIME)
from A LEFT jOIN B ON A.caseID = B.caseID
group by A.CaseID
group by B.cardid
a.*,b.handletime
from
a,b as t
where
a.caseId=t.caseId
and
not exists(select 1 from b where caseId=t.caseId and handleTime>t.handleTime)
select
b.cardid,max(b.handleTime) as handleTime
from
b
join
a
on
a.caseId=b.caseId
group by
b.cardid
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( caseId int,caseNumber varchar(20))
go
insert tb SELECT
1001, 'FS0010'
goIF OBJECT_ID('tc') IS NOT NULL
DROP TABLE tc
GO
CREATE TABLE tc( caseId int,handleTime datetime)
go
insert tc SELECT
1001 , '2008-08-12 15:00:00' union all select
1001 , '2008-08-13 16:00:00' select a.caseId,b.handleTime from tb a
join
(select * from tc t
where not exists(select * from tc where caseId=t.caseId and handleTime>t.handleTime)
) b
on a.caseId=b.caseIdcaseId handleTime
----------- -----------------------
1001 2008-08-13 16:00:00.000(1 行受影响)
FROM A
INNER JOIN B B1 ON A.CASEID=B1.CASEID
AND
NOT EXISTS
(SELECT 1 FROM B B2 WHERE B2.CASEID=B1.CASEID AND B2.HANDLETIME>B1.HANDLETIME)
还是习惯编译一下,不然拿不准select a.caseId,a.caseNumber,b.handleTime from tb a
join
(select * from tc t
where not exists(select * from tc where caseId=t.caseId and handleTime>t.handleTime)
) b
on a.caseId=b.caseIdcaseId caseNumber handleTime
----------- -------------------- -----------------------
1001 FS0010 2008-08-13 16:00:00.000(1 行受影响)
a.*,b.handletime
from
a,b as t
where
a.caseId=t.caseId
and
and handleTime=(select max(handleTime) from b where caseId=t.caseId)
DROP TABLE tb
GO
--借用小卒的数据
CREATE TABLE tb(caseId int,caseNumber varchar(20))
go
insert tb SELECT
1001, 'FS0010'
goIF OBJECT_ID('tc') IS NOT NULL
DROP TABLE tc
GO
CREATE TABLE tc(caseId int,handleTime datetime)
go
insert tc SELECT
1001 , '2008-08-12 15:00:00' union all select
1001 , '2008-08-13 16:00:00' select a.caseId,a.caseNumber,max(b.handleTime)
from tb as a
join tc as b
on a.caseId=b.caseId
group by a.caseId,a.caseNumber/*
caseId caseNumber
----------- -------------------- -----------------------
1001 FS0010 2008-08-13 16:00:00.000(1 行受影响)
*/
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
GO
CREATE TABLE ta( caseId int,caseNumber varchar(20))
go
insert ta SELECT
1001, 'FS0010'
goIF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( caseId int,handleTime datetime)
go
insert tb SELECT
1001 , '2008-08-12 15:00:00' union all select
1001 , '2008-08-13 16:00:00' select
a.*,t.handletime
from
ta a,tb as t
where
a.caseId=t.caseId
and
not exists(select 1 from tb where caseId=t.caseId and handleTime>t.handleTime)
/*caseId caseNumber handletime
----------- -------------------- ------------------------------------------------------
1001 FS0010 2008-08-13 16:00:00.000(所影响的行数为 1 行)
*/