数据库 - mysql联表去重查询

问题描述

现有两张表：

分类表A ：

id·······cate_name1········mysql2········php3·······java4·······ios

文章表B ：

id····cateid···content····time1·······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 表关联查询出每个分类下的最新发表的文章和对应的文章分类呢？求 具体的查询语句

问题解答

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

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