表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
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
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 行受影响)
LEFT JOIN B ON A.APID=B.AFID ORDER BY B.BDATE