主题:微软面试题：SQL查询问题，哪位高手有兴趣？

Problem: delete the post and the whole sub tree. (20 minutes)

Condition:

Supposing we have a web forum system, in which there are numbers of topics. Every topic has one and only one root post. Every post (either root post or intermediate post) has numbers of children posts, unless it\'s a leaf post. In short, you could regard a forum as a forest: A topic is a tree; a post is a tree node.

Posts are stored in the tbl_Posts table in SQL Server 2000. Definition of tbl_Posts is:

CREATE TABLE [dbo].[tbl_Posts] (
[PostID] [int] NOT NULL ,
[ParentID] [int] NOT NULL ,
[TopicID] [int] NOT NULL ,
[Su**ect] [nvarchar] (50) NOT NULL ,
[Body] [nvarchar] (1000) NOT NULL
)

ALTER TABLE [dbo].[tbl_Posts] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_Posts] PRIMARY KEY CLUSTERED
(
[PostID]
)

CREATE INDEX [IX_tbl_Posts] ON [dbo].[tbl_Posts]([ParentID])

ALTER TABLE [dbo].[tbl_Posts] ADD
CONSTRAINT [FK_tbl_Posts_tbl_Posts_1] FOREIGN KEY
(
[TopicID]
) REFERENCES [dbo].[tbl_Posts] (
[PostID]
),
CONSTRAINT [FK_tbl_Posts_tbl_Posts_2] FOREIGN KEY
(
[ParentID]
) REFERENCES [dbo].[tbl_Posts] (
[PostID]
)
Posts are sorted by PostID in the table. PostID is unique. All the posts in the same topic have the same TopicID, which equal to the root post\'s PostID. Root post\'s ParentID is equal to it\'s PostID. There is no limitation to depth of post tree.

The date in tbl_Posts might be:

PostID ParentID TopicID
1 1 1
2 1 1
3 3 3
4 1 1
5 1 1
6 6 6
7 4 1
8 4 1
9 1 1
10 3 3
11 4 1
12 3 3
13 12 3
14 6 6
15 6 6
16 9 1
17 8 1


Question:
Please write a SQL stored procedure \"DeletePost\", which takes a PostID as input parameter and can delete all the posts (include itself) under the post PostID. Note: using temp table, table variable or any recursive algorithm won\'t score. Cursor is not encouraged.
For example, \"exec DeletePost 4\" will change the forum as:

http://www.lazybones.biz

数据结构

和谐副警长

貌似用一个递归就OK了

我是开水，我找方便面。
你在哪里？再找不到你我就要冷特勒...
变成冰水了。

这个家伙很懒，什么也没留下......

It's so easy, Let him know that SQL Server 2005 will support recursive t-sql seelct statement: WITH

那么不如，让我们用力的拥抱，真心的祝福，然后转过身去，寻找自己的归宿。不管是哪一种朋友，有怎样的结局

哈刚白刚

[url=http://www.1110.net/down.asp?UserID=426123]全国各类考试复习“视频教学下载网”[/url]

偶4年前的写得一个论坛就是这样的结构，
微软的题目真火星

不自由，吾宁死

13楼正街，等我有空来想一个答案好了~~

那么不如，让我们用力的拥抱，真心的祝福，然后转过身去，寻找自己的归宿。不管是哪一种朋友，有怎样的结局

 我不是一个随便的人，但我随便起来就不是人