求一SQL语句,谢谢!

[dzmiao]

t1 --主题

id--cid--title
1---1----a
2---2----b
3---1----c
4---2----d
5---3----e
6---2----f
7---3----g
8---3----h
9---1----i

t2 --分类

cid--cate
1----kk
2----mm
3----dd

=========如何取每个分类的最新两条记录,即:


3---1----c
4---2----d

6---2----f
7---3----g
8---3----h
9---1----i


谢谢!

[da21]

楼上的select * from t1 a,
中的
t1 a
是什么意思？？？？？？？？

[dzmiao]

好久没写，手生了，试一下：

select * from t1 a
where id in (select top 2 id from t1 where cid = a.cid)

---------------------------以上返回结果不对

select * from t1 a,t2 b
where a.cid=b.cid
and a.id in(
select top 2 id from t1 where cid=b.cid order by id desc)


select * from t1 a where (select count(1) from t1 where cid=a.cid and id>=a.id) between 1 and 2

---------------------------以上正确

select * from t1 a
where id in (select top 2 id from t1 where cid=a.id order by id desc)


--------------------------以上无记录返回

select * from t1 a,
(select top 2 id from t1 where cid=a.id order by id desc) b
where a.id=b.id

--------------------------服务器: 消息 107，级别 16，状态 2，行 1
列前缀 'a' 与查询中所用的表名或别名不匹配。


感谢各位!

[zhang_yzy]

select
a.id,a.cid,a.title
from
t1 a
where
(select
count(1)
from
t1 b
where
b.cid =a.cid
and b.id >a.id ) <1

[txlicenhe]

好久没写，手生了，试一下：

select * from t1 a
where id in (select top 2 id from t1 where cid = a.cid)

[zjcxc]

select * from t1 a,t2 b
where a.cid=b.cid
and a.id in(
select top 2 id from t1 where cid=b.cid order by id desc)

[chinaandys]

select * from t1 a
where id in (select top 2 id from t1 where cid=a.id order by id desc)

[didoleo]

select * from t1 a where (select count(1) from t1 where cid=a.cid and id>=a.id) between 1 and 2

[alipaymate]

学习一下

[ReViSion]

select * from t1 a,
(select top 2 id from t1 where cid=a.id order by id desc) b
where a.id=b.id