菜鸟求简单查询

[wyman25]

表 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?

[wyman25]

忘记补充了：只用一条select 语句

[guguda2008]

SELECT A.CASEID,A.CASENUMBER,B1.HANDLETIME
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)

[JonasFeng]

select A.CaseID,max(b.HandleTime) as HandleTime 
from A LEFT jOIN B ON A.caseID = B.caseID 
group by A.CaseID 

[ws_hgo]

select B.cardid,max(B.handleTime) handleTime from B join A on A.caseId=B.caseId
group by B.cardid

[guguda2008]

深夜只有小喽啰留守CSDN，不会有别人来了，LZ直接结帖吧

[--小F--]

select 
  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)

[黄_瓜]

[Quote=引用 5 楼 guguda2008 的回复:]
深夜只有小喽啰留守CSDN，不会有别人来了，LZ直接结帖吧
[/Quote]

[--小F--]

或者
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'  
go

IF 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.caseId

caseId      handleTime
----------- -----------------------
1001        2008-08-13 16:00:00.000

(1 行受影响)

[guguda2008]

喵的刚打完一句话冒出来一堆人

[黄_瓜]

SELECT A.CASEID,A.CASENUMBER,B1.HANDLETIME
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.caseId

caseId      caseNumber           handleTime
----------- -------------------- -----------------------
1001        FS0010               2008-08-13 16:00:00.000

(1 行受影响)

[xiequan2]

select 
  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)

[黄_瓜]

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'  
go

IF 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 行受影响)


*/

[feixianxxx]

慢慢来 你们

[--小F--]

---都在验证 我也借小卒的数据来验证下
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'  
go

IF 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 行）
*/