有表a,内有字段ID,UserID,ReplyID
每一条留言是一条记录,但是对留言的回复也在这张表,为什么这样设计是历史原因,我不能改以前设计。
现在需要对ID排序,但是某条留言的回复必须跟在这条留言后面,主题留言的ReplyID为0
比如:
1 10000 0
2 10001 0
3 10002 1
4 10002 2
排序后得到
1 10000 0
3 10002 1
2 10001 0
4 10002 2
求解
每一条留言是一条记录,但是对留言的回复也在这张表,为什么这样设计是历史原因,我不能改以前设计。
现在需要对ID排序,但是某条留言的回复必须跟在这条留言后面,主题留言的ReplyID为0
比如:
1 10000 0
2 10001 0
3 10002 1
4 10002 2
排序后得到
1 10000 0
3 10002 1
2 10001 0
4 10002 2
求解
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-13 09:50:28
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([ID] int,[UserID] int,[ReplyID] int)
Insert tb
Select 1,10000,0 union all
Select 2,10001,0 union all
Select 3,10002,1 union all
Select 4,10002,2
Go
--Select * from tb-->SQL查询如下:
select * from tb order by case [ReplyID] when 0 then [ID] else [ReplyID] end,ID
/*
ID UserID ReplyID
----------- ----------- -----------
1 10000 0
3 10002 1
2 10001 0
4 10002 2(4 行受影响)
*/
比如
3 10002 1
就是
1 10000 0
的回复
/***********************************************--> 测试数据:[TB]
--> 测试时间:2009-08-13 09:51:54
--> 我的淘宝:<<戒色坊>> http://shop36766744.taobao.com/***********************************************/if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[UserID] int,[ReplyID] int)
insert [TB]
select 1,10000,0 union all
select 2,10001,0 union all
select 3,10002,1 union all
select 4,10002,2select * from [TB]
order by (case when ReplyID=0 then ID else ReplyID end),ID/*
ID UserID ReplyID
----------- ----------- -----------
1 10000 0
3 10002 1
2 10001 0
4 10002 2(4 行受影响)*/
drop table TB
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[UserID] int,[ReplyID] int)
insert [TB]
select 1,10000,0 union all
select 2,10001,0 union all
select 3,10002,1 union all
select 4,10002,2
SELECT * FROM TBCREATE TABLE TB2([ROWNUM] INT IDENTITY(1,1),[ID] int,[UserID] int,[ReplyID] int)DECLARE @ROW INT
SELECT @ROW=COUNT(1) FROM TB
SELECT @ROW
DECLARE @RC INTINSERT INTO TB2 SELECT TOP 1 * FROM TB
SET @RC=1
SET @ROW=@ROW-1
WHILE(@ROW>0)
BEGIN
IF EXISTS (SELECT * FROM TB WHERE TB.ReplyID=(SELECT ID FROM TB2 AS T WHERE @RC=T.ROWNUM))
BEGIN
INSERT INTO TB2 SELECT * FROM TB WHERE TB.ReplyID=(SELECT ID FROM TB2 AS T WHERE @RC=T.ROWNUM)
SET @RC=@RC+1
SET @ROW=@ROW-1
END
ELSE
BEGIN
INSERT INTO TB2 SELECT * FROM TB WHERE ReplyID=0 AND ID >(SELECT MAX(ID) FROM TB2 WHERE TB2.ReplyId=0)
SET @RC=@RC+1
SET @ROW=@ROW-1
END
END
SELECT * FROM TB2DROP TABLE TB2
DROP TABLE TB