我有多表联合查询,里面有LEFT OUTER JOIN和GROUP BY,排序里有MAX或ISNULL这样的条件,请问有什么好的存储过程吗?
SELECT A.status, A.id, A.subject AS 主题, A.writeName AS 发帖人,
A.writeDate AS 发帖时间, COUNT(B.forumSubjectID) AS 回复次数, MAX(B.restoreDate)
AS 最后回复时间
FROM forumSubject A LEFT OUTER JOIN
forumRestore B ON A.id = B.forumSubjectID
GROUP BY A.status, A.id, A.subject, A.writeName, A.writeDate
ORDER BY MAX(B.restoreDate) DESC, A.writeDate DESC
-------------------------------------------------------
SELECT A.*, B.restoreDate AS 回复时间, B.回复次数 AS 回复次数
FROM forumSubject A LEFT OUTER JOIN
(SELECT forumSubjectID, MAX(restoreDate) AS restoreDate, COUNT(*)
AS 回复次数
FROM forumRestore
GROUP BY forumSubjectID) B ON A.id = B.forumSubjectID
ORDER BY ISNULL(B.restoreDate, '1900-01-01') DESC, A.writeDate DESC
--------------------------------------------------------
以上2句SQL在数据库里运行是可以的,但没有配套的存储过程调用
我的要求是:
在一个SQL SERVER 2000数据库中有A,B两个表,
A表存储帖子id,帖子标题,发表人,发表时间,发表内容,
B表存储回复id,父贴id(等同于A中的帖子id),回复时间,回复内容
现在打算显示一个帖子列表,显示下列信息:
帖子标题|发表人|回复次数|最后回复时间|发帖时间
先显示最新发布的帖子,但如果有回复帖子的回复的时间大于最新发布的帖子,那么还是先显示最新回复的帖子
就和CSDN里的帖子显示的效果一样的,
我有多表联合查询,里面有GROUP BY,排序里有MAX或ISNULL这样的条件,请问有什么好的存储过程吗?