MYSQL数据库,求一SQL语句,每个类别取最新一条信息。表一: data_class(分类表,有两个字段)字段: ID ClassName
内容: 1 类别名一
2 类别名二
3 类别名三
4 类别名四表二: data_news(内容表,有四个字段,字段BigClass对应data_class表的ID字段)字段: ID Title BigClass PostTime
内容: 1 AAAAA 2 2007-02-01
2 BBBBB 1 2007-03-04
3 CCCCC 1 2007-04-05
4 DDDDD 3 2007-04-05
5 EEEEE 4 2007-04-05
6 FFFFF 3 2007-04-06
7 IIIII 2 2007-04-07我想按录入时间PostTime倒序,查类别BigClass为(1,2,3)这3个类的信息,每个类的信息只查最新一条。
内容: 1 类别名一
2 类别名二
3 类别名三
4 类别名四表二: data_news(内容表,有四个字段,字段BigClass对应data_class表的ID字段)字段: ID Title BigClass PostTime
内容: 1 AAAAA 2 2007-02-01
2 BBBBB 1 2007-03-04
3 CCCCC 1 2007-04-05
4 DDDDD 3 2007-04-05
5 EEEEE 4 2007-04-05
6 FFFFF 3 2007-04-06
7 IIIII 2 2007-04-07我想按录入时间PostTime倒序,查类别BigClass为(1,2,3)这3个类的信息,每个类的信息只查最新一条。
select * from data_news
where BigClass in ('1', '2', '3') and
ID in (select max(ID) from data_news group by BigClass ) and
PostTime in (select max(PostTime) from data_news group by BigClass )
order by PostTime desc
HTTP://www.ebigear.com/Fund/PlayNews.php?NewsID=29453&ID=285885
select data_news.* from data_news,
(select BigClass, Max(PostTime) as PostTime1 from data_news
where BigClass in ('1', '2', '3')
group by BigClass
) a
where data_news.BigClass=a.BigClass and data_news.PostTime=a.PostTime1
order by data_news.PostTime desc