问一个sql的写法

[christinadingkou]

表tbl，存放bbs里的帖子
字段：
fld_id,帖子编号
fld_pid，父帖编号，如果是父帖则此字段值＝0
fld_body,帖子内容
fld_createtime,发帖时间

一个父帖可以有多个子帖，
确认父帖的方法是，fld_pid=0,
确认父帖和子帖关系的方法是:a记录的fld_pid = b记录的fld_id,则b是a的父帖.

我现在想得到一个结果集,全部都是父帖,并且按照它的最新子帖的发布时间降序排列

例如:
fld_id,fld_pid,fld_body,fld_createtime
1 0 1 2006-01-02
2 0 2 2006-01-01
3 1 3 2006-01-03
4 1 4 2006-01-04
5 2 5 2006-01-05
6 2 6 2006-01-06

则得到的结果集是:
fld_id,fld_body
2 2
1 1

关键是得按照子帖的发布时间排序,谢谢高人指点~~~

[christinadingkou]

自己up

[小李木耳]

试试：
--子帖的最大时间sql:
select t1.fld_pid，max(t1.fld_createtime) as max_time
from tbl t1, tbl t2
where t1.fld_pid in (select distinct t2.fld_id from tbl t2)
group by t1.fld_pid

--楼主需要的sql:
select a.fld_id, fld_body
from tbl a， (select t1.fld_pid，max(t1.fld_createtime) as max_time
from tbl t1, tbl t2
where t1.fld_pid in (select distinct t2.fld_id from tbl t2)
group by t1.fld_pid) b
where a.fld_pid = 0 --父
and a.fld_id = b.fld_pid
order by b.max_time desc

[小李木耳]

修正一点：
select a.fld_id, fld_body
----->
select a.fld_id, a.fld_body

[christinadingkou]

谢谢