表1的结构是
id title
1 aaa
2 bbb
3 ccc表2的结构是
id cid content atime
1 1 aaa 2008-6-10 15:40:29
2 1 aaa 2008-6-10 15:42:29
3 2 aaa 2008-6-10 15:43:29
4 1 aaa 2008-6-10 15:45:29
5 3 aaa 2008-6-10 15:12:29
6 2 aaa 2008-6-10 15:23:29
6 2 aaa 2008-6-10 15:53:29
6 3 aaa 2008-6-10 15:11:29 表2里面的cid对应表1的id
现在想得到的结果是根据表2里面的atime最后更新的值 给表1里面的id排序读出来
比如这样
表1结果
3 ccc
1 aaa
2 bbb
id title
1 aaa
2 bbb
3 ccc表2的结构是
id cid content atime
1 1 aaa 2008-6-10 15:40:29
2 1 aaa 2008-6-10 15:42:29
3 2 aaa 2008-6-10 15:43:29
4 1 aaa 2008-6-10 15:45:29
5 3 aaa 2008-6-10 15:12:29
6 2 aaa 2008-6-10 15:23:29
6 2 aaa 2008-6-10 15:53:29
6 3 aaa 2008-6-10 15:11:29 表2里面的cid对应表1的id
现在想得到的结果是根据表2里面的atime最后更新的值 给表1里面的id排序读出来
比如这样
表1结果
3 ccc
1 aaa
2 bbb
from 表1 as a
inner join
(
select cid,max(atime) as atime from 表2 group by cid
) as b on a.id=b.cid order by b.atime
from 表1 as a
inner join
(
select cid,max(atime) as atime from 表2 group by cid
) as b on a.id=b.cid order by b.atime
from 表1 a
join (select cid, atime=max(atime) from 表2 group by cid) b on a.id=b.cid
order by atime
(
id int,
title char(3)
)
insert into a
select
1,'aaa' union select
2,'bbb' union select
3,'ccc'
create table b
(
cid int,
atime smalldatetime
)
insert into b
select
1,'2008-6-10 15:40:29' union select
1,'2008-6-10 15:42:29' union select
2,'2008-6-10 15:43:29' union select
1,'2008-6-10 15:45:29' union select
3,'2008-6-10 15:12:29' union select
2,'2008-6-10 15:23:29' union select
2,'2008-6-10 15:53:29' union select
3,'2008-6-10 15:11:29'
select a.*
from a inner join (select cid,max(atime) as 'atime' from b group by cid) c on c.cid = a.id
order by c.atime
结果
id title
----------- -----
3 ccc
1 aaa
2 bbb(3 row(s) affected)
多谢