一个类似发贴回帖的排序问题

一个类似发贴回帖的排序问题

快过年了，没事上到csdn,看到有一问题，顶有意思，想了很久，终于可以找到一个解决的方法，虽然问题主人已经结帖，没有分数，但还是回个帖子，毕竟自己辛苦的结果嘛。也顺便来来这里与大家分享一下。
原来问题：

[@more@] 一个类似发贴回帖的排序问题

快过年了，没事上到csdn,看到有一问题，顶有意思，想了很久，终于可以找到一个解决的方法，虽然问题主人已经结帖，没有分数，但还是回个帖子，毕竟自己辛苦的结果嘛。也顺便来来这里与大家分享一下。
原来问题：
TABLE [A] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[t_date] [datetime] NULL ,
[type_code] [int] NULL ,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [B] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[t_date] [datetime] NULL ,
[type_code] [int] NULL ,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into A values('1','1900-01-02','0' )
insert into A values('2','1900-01-03','0' )
insert into A values('3','1900-01-04','0' )
insert into A values('4','1900-01-05','0' )
insert into A values('5','1900-01-06','0' )
insert into A values('6','1900-01-07','0' )
insert into B values('1','1900-02-02','3' )
insert into B values('2','1900-02-03','2' )
insert into B values('3','1900-02-04','1' )
insert into B values('4','1900-02-05','5' )
insert into B values('5','1900-02-06','5' )
insert into B values('7','1900-02-07','1' )
insert into B values('8','1900-02-08','1' )
insert into B values('9','1900-02-09','1' )
insert into B values('10','1900-02-10','1' )
insert into B values('11','1900-02-11','2' )
insert into B values('12','1900-02-12','2' )
insert into B values('13','1900-02-13','2' )
insert into B values('14','1900-02-14','4' )
两表的关系是 A.id=B.type_code
现在我能得到的是
select id,t_date,type_code from(
select distinct A.*,A.id as asp from A,B
where A.id=B.type_code
union all
select B.*,B.type_code as asp from A,B
where A.id=B.type_code ) as bb
order by asp desc,type_code
结果如下：
id t_date type_code
------------------------------------------------------------------
51900-01-09 00:00:00.0000
41900-02-05 00:00:00.0005
51900-02-06 00:00:00.0005
41900-01-05 00:00:00.0000
141900-02-14 00:00:00.0004
31900-01-04 00:00:00.0000
11900-02-02 00:00:00.0003
21900-01-03 00:00:00.0000
21900-02-03 00:00:00.0002
111900-02-11 00:00:00.0002
121900-02-12 00:00:00.0002
131900-02-13 00:00:00.0002
11900-01-02 00:00:00.0000
31900-02-04 00:00:00.0001
91900-02-09 00:00:00.0001
101900-02-10 00:00:00.0001
71900-02-07 00:00:00.0001
81900-02-08 00:00:00.0001
现在我想知道怎么按时间排序，这是一个bbs类似的标题与回复问题
但不同的是我想让回复的时间的来排列 看上去数据比较冗余 要是心情好的时候 帮我看看 sql怎么写
---------想要的东西如下------------
id t_date type_code
-----------------------------------
41900-01-05 00:00:00.0000
141900-02-14 00:00:00.0004
21900-01-03 00:00:00.0000
21900-02-03 00:00:00.0002
111900-02-11 00:00:00.0002
121900-02-12 00:00:00.0002
131900-02-13 00:00:00.0002
11900-01-02 00:00:00.0000
31900-02-04 00:00:00.0001
91900-02-09 00:00:00.0001
101900-02-10 00:00:00.0001
71900-02-07 00:00:00.0001
81900-02-08 00:00:00.0001
51900-01-09 00:00:00.0000
41900-02-05 00:00:00.0005
51900-02-06 00:00:00.0005
31900-01-04 00:00:00.0000
11900-02-02 00:00:00.0003

/*
思想：
查询1：把最后回复的帖子排序在前面（按降），而且每一贴的最后回复放在前面(不包含主帖，按降序)
查询2：把最后回复的帖子排序在前面(按降)，回贴按回帖日期排序（升序）
（这是通常的论坛查询方式）
*/
查询1：
SELECT * INTO #Temp FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.id=B.type_code)
UNION ALL
SELECT * FROM B WHERE EXISTS(SELECT 1 FROM A WHERE A.id=B.type_code)
SELECT A1.*
FROM #Temp AS A1 INNER JOIN
(SELECT type_code,MAX(t_date) AS t_date FROM B GROUP BY type_code) AS B1 ON B1.type_code=(CASE A1.type_code WHEN 0 THEN A1.id ELSE A1.type_code END)
ORDER BY B1.t_date desc,A1.type_code,A1.t_date desc
DROP TABLE #Temp
查询2：
SELECT * INTO #Temp FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.id=B.type_code)
UNION ALL
SELECT * FROM B WHERE EXISTS(SELECT 1 FROM A WHERE A.id=B.type_code)
SELECT A1.*
FROM #Temp AS A1 INNER JOIN
(SELECT type_code,MAX(t_date) AS t_date FROM B GROUP BY type_code) AS B1 ON B1.type_code=(CASE A1.type_code WHEN 0 THEN A1.id ELSE A1.type_code END)
ORDER BY B1.t_date desc,A1.type_code,A1.t_date
DROP TABLE #Temp

结果1：

结果2：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7764484/viewspace-898174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7764484/viewspace-898174/