这个要怎么写?

[cdwfeng]

两张表：T1和T2（部分字段被省略）

T1(发表的文章)
-------------------------------
ID (发表的文章的ID号) TIME1
a 2001-12-12 05:25:25
b 2001-12-13 05:25:25
c 2001-12-11 05:25:25
d 2001-12-16 05:25:25

T2 (回复的文章)
----------------------------------------
TIME2 pID (已回复的文章的ID号)
2001-12-12 05:25:25 a
2001-12-13 04:20:25 b
2001-12-11 03:20:25 a
2001-12-16 12:25:25 c
2001-12-17 13:25:25 b
2001-12-19 01:25:25 a
2001-12-18 14:25:25 c

现在要对T1的ID字段排序（ID字段不重复），排序的依据是时间。
具体要求：1、T2中没有T1中相同的ID时（如上面的ID=d,在T2中不存在），用T1的TIME1时间作为排序时间,要求时间按倒序排列。
2、T1中的某个ID在T2中已存在，则取出T2中pID对应于ID的离现在最近的那个时间,作为那个ID的排序时间,要求时间按倒序排列。
3、只取最近的10条。

谢谢！很急！

[cdwfeng]

这个要怎么写,帮帮我吧,各位大师...着急呀

[dugucan]

帮你顶！

[rankisky]

select t1.id,t2.pid from t1,t2 where t1.id <> t2.pid order by t1.time1 desc

这个？

[sober001]

select t1.id from t1 where t1.id not in (select pid from t2) order by t1.time1 desc
不明白你第二个要求是什么意思.

[街头小贩]

是不是一个按时间倒序的外连接？
SELECT 你哪哪些列自已定义吧 FROM T1 LEFT OUTER JOIN T2 ON T1.ID=T2.ID ORDER BY T1.TIME1,T2.TiME2 DESC

[Atai-Lu]

if not object_id('tblist') is null drop view tblist
go

create view [dbo].[tblist] as

select t1.id,t1.time1 from t1,t2 where t1.id<>t2.pid
union
select t1.id,t2.time2 as time1 from t1,t2 where t1.id=t2.pid

go
--------然后从视图里查询并排序
select * from tblist order by time1 desc

[Atai-Lu]

if not object_id('tblist') is null drop view tblist
go

create view [dbo].[tblist] as

select t1.id,t1.time1 from t1 where id not in(select pid from t2 where pid=t1.id)
union
select t1.id,(select top 1 time2 from t2 where pid=t1.id order by time2 desc) as time1 from t1 where t1.id in(select pid from t2 where pid=t1.id)
go

select top 10 * from tblist order by time1 desc

[cdwfeng]

先谢谢大家了!我忘了说这个是用的ACCESS数据库,要求实际上就是:先把符合要求1的排完后,再排符合要求2的,总数只取最前面的10条.

[Atai-Lu]

--这个你试试，我没测试过
select t1.id,t1.time1,(select top 1 time2 from t2 where pid=t1.id order by [time2] desc) as time2 from t1
	order by time2 desc,t1.time1 desc

[Atai-Lu]

select top 10 t1.id,t1.time1,(select top 1 time2 from t2 where pid=t1.id order by [time2] desc) as time2 from t1
    order by time2 desc,t1.time1 desc

[cdwfeng]

<%


sql="select top 10 t1.id,t1.time1,(select top 1 time2 from t2 where pid=t1.id order by [time2] desc) as time2 from t1 order by time2 desc,t1.time1 desc"
rs.open sql,conn,1,1
if not rs.eof and not rs.bof then
for i=1 to 10
%>

Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e21'

ODBC 驱动程序不支持所需的属性。

/265/index.asp，行 639
不知道是怎么回事?谢谢呵!

[cdwfeng]

还有:ID在数据表里是自动编号的,而PID不是,这个有没有什么关系呀?

[Atai-Lu]

--sql server查询分析器里可以执行...
select top 1 t1.tid,t1.addtime,(select top 1 addtime from ReviewReply where tid=t1.tid order by [addtime] desc) as addtime2 from ReviewTab as t1
	order by addtime2 desc,t1.addtime desc
--你改成下面的试试
select top 10 t1.[id],t1.[time1],(select top 1 time2 from t2 where [pid]=t1.id order by [time2] desc) as [time3] from t1 order by [time3] desc,t1.[time1] desc

[zzxap]

T1中的某个ID在T2中已存在，则取出T2中pID对应于ID的离现在最近的那个时间,作为那个ID的排序时间,要求时间按倒序排列。
select a.* from t2 a where time2 = (select max(time2) from t2 where pid = a.pid) order by a.time2 desc

[jhwcd]

select t1.id from t1 where t1.id not in (select pid from t2) order by t1.time1 desc 

还是有点晕。

[Atai-Lu]

楼上的，楼主大概的意思是：
一个主题表t1存放主题，一个回复表t2主题的回复内容

查询出主题表的内容，如果主题有回复，则按回复时间排序，没有回复，按添加时间排序

[cdwfeng]

太对了,就是这意思,它实际是一个,不知道是不是其它在方有问题.