select a.* from (select top 10 pid,max(时间)时间0 from b group by pid order by 时间0 desc)aa
left join a on aa.pid=a.编号 order by aa.时间0 DESC
就忘了desc,不行吗?
select top 10 pid,max(时间)时间0 from b group by pid order by 时间0 desc
是不同新闻评论取最大时间,降序后取前10条
left join a on aa.pid=a.编号
然后去找新闻表(按编号)找到后按评论时间降序order by aa.时间0 DESC
insert into a
select '060801','IT新型工程','据国外媒体报道.......'
union all
select '060802','内幕调查','《世界新闻报》记者在网站上....'
union all
select '060803','CSDN专访 ',' 波折路上的坚持行进者...'
union all
select '060804','我们为什么要睡眠?','我们为什么要睡眠?........'
insert into b
select 'P60808','060802','评:内幕调查','2007-06-08 11:00:00'
union all
select 'P60802','060801','评:IT新型工程','2007-06-08 07:00:00'
union all
select 'P30909','060804','评:我们为什么要睡眠','2007-03-09 11:21:00'
union all
select 'P60809', '060801','再评:IT新型工程','2007-06-08 17:00:00'
select * from a
where pid in(select PID from b )
order by (select max(ptime) from b where b.pid=a.pid) desc