select * from table where ParentID=0 order by PDate desc
select * from tablename where prentid=0 order by pdate desc
因为SQL语句都是先执行排序,再执行查询!
select * from tablename where pid in (select distinct parentid from tablename where parentid!=0 order by pdate desc)
很明显,这个子查询用了distinct,排序就有问题了
select * form (select * from table where PDate in (select max(PDate) from table group by ParentID ) )where ParentID='0'
表的设计好像很有问题 你就每个回帖时间的字段 怎么排序呢 难道回帖的时间也插入到 pDate里面吗
select * from tableName where ParentID=0 order by PDate desc 当parentID不为0时,PDate 就为回帖时间了,而不在是发帖时间
利用这种方式构造成两个表进行关联 select * form a, (select 列1,列2 from .....) b where a.字段= b.字段 order by b.字段.... 虚构的b表可以查出最后回帖时间... 懒得写了,lz可以再去想想
drop table tab1create table tab1 ( pid int, parentid int, pcontent varchar(50), pdate datetime, )insert into tab1 select 1,0,'发帖1','2012-8-5' union all select 2,0,'发帖2','2012-8-5' union all select 3,1,'回帖1','2012-8-5' union all select 4,2,'回帖2','2012-8-5' union all select 5,0,'发帖3','2012-8-6' union all select 6,5,'回帖3','2012-8-7'select * from tab1select a.pid,a.parentid,a.pcontent,a.pdate,(select max(pdate) from tab1 where parentid=a.pid) reply from tab1 a where a.parentid =0 order by reply desc
或者select a.pid,a.parentid,a.pcontent,a.pdate,b.pdate from tab1 as a inner join tab1 as b on b.parentid=a.pid where a.parentid=0 order by b.pdate desc
(select distinct parentid from tablename where parentid!=0 order by pdate desc)
很明显,这个子查询用了distinct,排序就有问题了
where PDate in (select max(PDate) from table group by ParentID ) )where ParentID='0'
当parentID不为0时,PDate 就为回帖时间了,而不在是发帖时间
利用这种方式构造成两个表进行关联
select * form a, (select 列1,列2 from .....) b where a.字段= b.字段 order by b.字段....
虚构的b表可以查出最后回帖时间...
懒得写了,lz可以再去想想
drop table tab1create table tab1
(
pid int,
parentid int,
pcontent varchar(50),
pdate datetime,
)insert into tab1
select 1,0,'发帖1','2012-8-5' union all
select 2,0,'发帖2','2012-8-5' union all
select 3,1,'回帖1','2012-8-5' union all
select 4,2,'回帖2','2012-8-5' union all
select 5,0,'发帖3','2012-8-6' union all
select 6,5,'回帖3','2012-8-7'select * from tab1select a.pid,a.parentid,a.pcontent,a.pdate,(select max(pdate) from tab1 where parentid=a.pid) reply
from tab1 a where a.parentid =0 order by reply desc