求一个变态的SQL语句的写法

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