34,838
社区成员




CREATE TABLE users(id INT IDENTITY(1,1),username VARCHAR(10),password VARCHAR(10))
CREATE TABLE newpost(postid INT IDENTITY(1,1),userid INT,title VARCHAR(100),message VARCHAR(1000),posttime DATETIME,replies INT)
CREATE TABLE reply(replyid INT IDENTITY(1,1),userid INT,message VARCHAR(1000),replyTime DATETIME,postid INT)
GO
INSERT users SELECT 'roy_','bbb'
UNION ALL SELECT 'limpire','ccc'
UNION ALL SELECT 'fcuandy','ddd'
INSERT newpost SELECT 1,'散分','散分需要理由吗','2007-1-1',2
UNION ALL SELECT 3,'每日一贴','不为别的,只为可用分','2007-2-1',0
INSERT reply SELECT 2,'sf','2007-1-2',1
UNION ALL SELECT 3,'bd','2007-1-3',1
GO
/*
SELECT * FROM users
SElECT * FROM newpost
SELECT * FROM reply
*/
GO
SELECT n.title 标题,u.userName 提问人,n.posttime 提问时间,n.replies 回复数,ISNULL(rt,n.posttime) 最后回复时间,ISNULL(un,'--') 最后回复人
FROM newpost n
INNER JOIN users u
ON u.id=n.userid
LEFT JOIN
(
SELECT r.replyTime rt,u.username un ,r.postid postid
FROM reply r
INNER JOIN users u
ON u.id = r.userid
INNER JOIN
(SELECT MAX(replyid) mrid,postid FROM reply GROUP BY postid) x
ON mrid = replyid AND x.postid = r.postid
) x
ON x.postid = n.postid
/*
标题 提问人 提问时间 回复数 最后回复时间 最后回复人
--------------------------------------------------------------------------------
散分 roy_ 2007-01-01 00:00:00.000 2 2007-01-03 00:00:00.000 fcuandy
每日一贴 fcuandy 2007-02-01 00:00:00.000 0 2007-02-01 00:00:00.000 --
*/
GO
DROP TABLE users,newpost,reply
GO
CREATE TABLE users(id INT IDENTITY(1,1),username VARCHAR(10),password VARCHAR(10))
CREATE TABLE newpost(postid INT IDENTITY(1,1),userid INT,title VARCHAR(100),message VARCHAR(1000),posttime DATETIME,replies INT,LastReplyTime DATETIME,LastReplyer VARCHAR(10),LastReplyerID INT)
/*
为什么加 LastReplyTime ? 减少连表操作,每次有人回复时,即将最新回复人更新到主题表.
为什么加 LastReplyer 和 LastReplyerID ? 道理同上
为什么要同时加 LastReplyer 和 LastReplyerID ,两者不是有一个就可以吗?
加 LastReplyer 为了在贴子列表处直接从 newpost 读出来,减少连表操作, 因为贴子列表,是一个论坛打开最频繁的操作.
那为什么还要加 LastReplyerID ? 没有别的原因,仅仅为了一个超连接的参数.比如 在回复人上点连接.
其实更可以甚至把 postUserName 也一起存进去,这样,在繁多的贴子列表时,又可以少连一次表,即贴子列表可以直接从一个表取出来了. 当然,这个例子中,我并没有这么做.
多了三四个字段,好似增加了数据冗余,但效率的提升很明显,并且,空间的占用也并不是很多,个人觉得以少许空间换大量时间,这样的牺牲是值得的.
*/
CREATE TABLE reply(replyid INT IDENTITY(1,1),userid INT,message VARCHAR(1000),replyTime DATETIME,postid INT)
GO
INSERT users SELECT 'roy_','bbb'
UNION ALL SELECT 'limpire','ccc'
UNION ALL SELECT 'fcuandy','ddd'
INSERT newpost SELECT 1,'散分','散分需要理由吗','2007-1-1',2,'2007-1-3','fcuandy',3
UNION ALL SELECT 3,'每日一贴','不为别的,只为可用分','2007-2-1',NULL,0,'',NULL
INSERT reply SELECT 2,'sf','2007-1-2',1
UNION ALL SELECT 3,'bd','2007-1-3',1
GO
/*
SELECT * FROM users
SElECT * FROM newpost
SELECT * FROM reply
*/
GO
SELECT n.title 标题,u.userName 提问人,n.posttime 提问时间,n.replies 回复数,n.LastReplyTime 最后回复时间,n.LastReplyer 最后回复人
FROM newpost n
INNER JOIN users u
ON u.id=n.userid
GO
DROP TABLE users,newpost,reply
GO
--对应关系是我猜的,下次最好写明白.
select a.title 标题 , b.username 提问人 , c.回复数 , c.最后更新
from newpost a,users b,
(
select postid , count(*) 回复数 , max(replytime) 最后更新 from newpost group by postid
) c
where a.userid = b.id and a.postid = c.postid