sql小问题！

[Small__Wolf]

表 bbscard

id name createtime parentid
1 aaa 2009-08-08 0
2 bbb 2009-08-08 0
3 ccc 2009-08-08 0
4 ddd 2009-08-09 1
5 eee 2009-08-29 2
6 fff 2009-08-19 1
7 ggg 2009-08-09 2

求一条sql语句，要求显示 按最新回复时间排序显示，正确结果如下

id name createtime parentid
1 bbb 2009-08-08 0
2 aaa 2009-08-08 0

[soft_wsx]

去重复处理！用EXISTS

[华夏小卒]

什么规律

[fenlin]

没看明白楼主想要什么结果

[Small__Wolf]

表 bbscard

id name createtime parentid
1 aaa 2009-08-08 0
2 bbb 2009-08-08 0
3 ccc 2009-08-08 0
4 ddd 2009-08-09 1
5 eee 2009-08-29 2
6 fff 2009-08-19 1
7 ggg 2009-08-09 2


其中
4 ddd 2009-08-09 1
5 eee 2009-08-29 2
6 fff 2009-08-19 1
7 ggg 2009-08-09 2
是回复ID 1，2的信息
要求查询出信息的时候只显示pid为0的记录，并且按照回复时间最新的排序

[小宏]

你说的和你想要显示的不明白

[soft_wsx]

如果是2005以前版本可以使用ROW——NUMBER（）

[--小F--]

那答案也不止2条啊 应该是3条

[oflying907]

select * from 表 where parentid=0 order by createtime desc
这样？

[ivws_19]

楼主是要把有回复的（这里只有前两条记录有回复）按回复日期排序显示出来

[ivws_19]

select distinct t.* from bbscard t, bbscard t1 where t.parentid=0 and t.id=t1.parentid order by t1.createtime

[翼帆]

create table #temp (
	id int null,
	name nvarchar(10) null,
	createtime datetime null,
	parentid int null
)
insert into #temp 
select 1,'aaa','2009-08-08',0 union all select 
      2, 'bbb' ,'2009-08-08',           0  union all select 
     3,  'ccc'  ,'2009-08-08',         0  union all select 
    4,   'ddd'   ,'2009-08-09',       1  union all select 
   5,    'eee'    ,'2009-08-29',     2  union all select 
  6,     'fff'     ,'2009-08-19',   1  union all select 
7,       'ggg'      ,'2009-08-09',2 

select *,(select MAX(createtime) from #temp where parentid = a.id) 最后回复时间  from #temp a
where exists(select 1 from #temp where  parentid = a.id)
order by 最后回复时间 desc 

---------------------------------
--2	bbb	2009-08-08 00:00:00.000	0	2009-08-29 00:00:00.000
--1	aaa	2009-08-08 00:00:00.000	0	2009-08-19 00:00:00.000

[翼帆]

select a.*,MAX(b.createtime) 最后回复时间 from #temp a,#temp b where a.parentid=0 and a.id = b.parentid
group by a.id,a.createtime,a.name,a.parentid
 order by 最后回复时间 desc

这样也成。

[ithhh2010]

create table bbscard(id int, name varchar(10), createtime datetime, parentid varchar(10) )


insert into bbscard select 1 , 'aaa' , '2009-08-08' , 0 union all select

2 , 'bbb' , '2009-08-08', 0 union all select
3 , 'ccc' , '2009-08-08' , 0 union all select
4 , 'ddd' , '2009-08-09', 1 union all select
5 , 'eee' , '2009-08-29', 2 union all select
6 , 'fff' , '2009-08-19', 1 union all select
7 , 'ggg' , '2009-08-09' , 2


结果如下：


select * from bbscard order by createtime desc