create table newstype
( typeid int,
parentid int
)
create table news
( id int,
typeid int,
title varchar(20)
)
insert into newstype values(1,5)
insert into newstype values(2,5)
insert into newstype values(3,5)
insert into newstype values(2,6)
insert into news values(1,1,'aaa');
insert into news values(2,1,'bbb');
insert into news values(3,1,'ccc');
insert into news values(4,2,'ddd');
insert into news values(5,2,'eee');
insert into news values(6,3,'fff');
insert into news values(7,3,'ggg');
select b.typeid,b.maxid,c.id,c.title from newstype a,
(select typeid,max(id) as maxid from news group by typeid) b,news c
where a.parentid=5 and a.typeid=b.typeid and b.maxid=c.id
输出:
typeid maxid id title
1 3 3 ccc
2 5 5 eee
3 7 7 ggg
我通过查询该类别的最大id来取数
下面是我的查询语句,大家帮看看(我要查的是大类id=5的所有小类最新一条数据)
select b.typeid,b.maxid,c.id,c.title from newstype a,
(select typeid,max(id) as maxid from news group by typeid) b,news c
where a.parentid=5 and a.typeid=b.typeid and b.maxid=c.id