****************
补充:没看到你的表中有回复数了已经,修改了一下。
补充2:测试了,修正了一下应该可以用。
****************
查询所有的回复:
select distinct a.Title,a.LeavewordPeople,a.CreateTime,a.ReplyNo,b.ReplyPeople
from
Leavewords a,Replies b,ReplyLeaveword c
where a.LeavewordId=c.LeavewordID and b.ReplyId=c.ReplyId;
查询最近一条的回复:
select a.Title,a.LeavewordPeople,a.CreateTime,a.ReplyNo,b.ReplyPeople
from
Leavewords a,Replies b,ReplyLeaveword c,
(select b.LeavewordID,max(a.CreateTime) 最近时间 from Replies a,ReplyLeaveword b where a.ReplyId =b.ReplyId group by b.LeavewordID) d
where a.LeavewordId=c.LeavewordID and b.ReplyId=c.ReplyId and a.LeavewordID=d.LeavewordID and b.CreateTime=d.最近时间;
****************
实施log:
[TEST@ORA1] SQL>select * from Leavewords;
LEAVEWORDID TITLE BODY LEAVEWORDP CREATETIME REPLYNO
----------- ---------- ---------- ---------- ---------- ----------
1 aaa aaaa a1 20090909 3
[TEST@ORA1] SQL>select * from Replies;
REPLYID BODY REPLYPEOPL CREATETIME
---------- ---------- ---------- ----------
1 aaaaR1 a1R1 20090909
2 aaaaR2 a1R2 20090910
3 aaaaR3 a1R2 20090911
4 aaaaR3 a1R3 20090911
[TEST@ORA1] SQL>select * from ReplyLeaveword;
REPLYID LEAVEWORDID
---------- -----------
1 1
2 1
3 1
4 1
[TEST@ORA1] SQL>select distinct a.Title,a.LeavewordPeople,a.CreateTime,a.ReplyNo
,b.ReplyPeople
2 from
3 Leavewords a,Replies b,ReplyLeaveword c
4 where a.LeavewordId=c.LeavewordID and b.ReplyId=c.ReplyId;
TITLE LEAVEWORDP CREATETIME REPLYNO REPLYPEOPL
---------- ---------- ---------- ---------- ----------
aaa a1 20090909 3 a1R1
aaa a1 20090909 3 a1R2
aaa a1 20090909 3 a1R3
[TEST@ORA1] SQL>select a.Title,a.LeavewordPeople,a.CreateTime,a.ReplyNo,b.ReplyP
eople
2 from
3 Leavewords a,Replies b,ReplyLeaveword c,
4 (select b.LeavewordID,max(a.CreateTime) 最近时间 from Replies a,ReplyLeavew
ord b where a.ReplyId =b.ReplyId group by b.LeavewordID) d
5 where a.LeavewordId=c.LeavewordID and b.ReplyId=c.ReplyId and a.LeavewordID
=d.LeavewordID and b.CreateTime=d.最近时间;
TITLE LEAVEWORDP CREATETIME REPLYNO REPLYPEOPL
---------- ---------- ---------- ---------- ----------
aaa a1 20090909 3 a1R2
aaa a1 20090909 3 a1R3
---
以上,希望对你有所帮助。
温馨提示:答案为网友推荐,仅供参考