一个查询的问题

[qiudong_5210]

表A为为问题表 ，表B为回复表，想取出最近回复的三个问题
B表中的AFID是A表中的APID

CREATE TABLE A (APID INT,ANAME VARCHAR(200),AUserId INT)
INSERT INTO A(APID,ANAME,AUserId)VALUES(1,'title1',25)
INSERT INTO A(APID,ANAME,AUserId)VALUES(2,'title2',25)
INSERT INTO A(APID,ANAME,AUserId)VALUES(3,'title3',27)
INSERT INTO A(APID,ANAME,AUserId)VALUES(4,'title4',25)

CREATE TABLE B (BPID INT,BNAME VARCHAR(200),BDate DATETIME,BUserID INT,AFID INT)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(1,'asdf1','2013-01-11 11:00:11',56,1)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(2,'asdf1','2013-01-12 11:00:11',99,1)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(3,'asdf1','2013-01-13 11:00:11',56,2)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(4,'asdf1','2013-01-14 11:00:11',58,2)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(5,'asdf1','2013-01-15 11:00:11',56,3)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(6,'asdf1','2013-01-16 11:00:11',78,3)
INSERT INTO B(BPID,BNAME,BDate,BUserID,AFID)VALUES(7,'asdf1','2013-01-17 11:00:11',64,1)


--最后想得到 结果如下
--APID ANAME BUserId
--1	title1	64
--3	title3	78
--2	title2	58 

[骑着蜗牛去爬山]

select apid,aname,buserid from (select *,row=row_number()over(partition by b.afid order by bdate desc) from a join b on a.apid=b.afid)t where row=1

[chuanzhang5687]

SELECT A.APID,A.ANAME,B.BUSERID
FROM A , 
(
	SELECT *
	FROM B T
	WHERE 
    NOT EXISTS(SELECT 1 FROM B WHERE B.AFID = T.AFID AND B.BDATE > T.BDATE)
) B
WHERE A.APID = B.AFID
ORDER BY B.BDATE DESC

/*
APID        ANAME     BUSERID
----------- --------- -------
1           title1    64
3           title3    78
2           title2    58

(3 行受影响)

[ahhfql]

学习

[猫侠]

select * from A LEFT JOIN B ON A.APID=B.AFID ORDER BY B.BDATE