數據庫 - 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