数据库 - mysql联表去重查询

Question

现有两张表：

1. 分类表A ：

   id·······cate_name
   1········mysql
   2········php
   3·······java
   4·······ios

2. 文章表B ：

   id····cateid···content····time
   1·······1·······xxx······时间戳
   2·······2·······xxx······时间戳
   3·······3·······xxx······时间戳
   4·······1·······xxx······时间戳
   5·······1·······xxx······时间戳
   6·······3·······xxx······时间戳

   现在想 select * from B left join A on B.cateid = A.id，当然这样会有重复的分类文章会查出来。怎么写SQL 能从B 表关联查询出每个分类下的最新发表的文章和对应的文章分类呢？求 具体的查询语句

Answer 1

你得先分组查询出每个分类下时间戳最大的文章然后再去和A联合查询

Answer 2

select tb.*,A.cate_name from  (select * from B order by cateid,time desc) tb left join A on tb.cateid = A.id group by tb.cateid