問題描述

現(xiàn)有兩張表：

分類表A ：

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

文章表B ：

id····cateid···content····time1·······1·······xxx······時(shí)間戳2·······2·······xxx······時(shí)間戳3·······3·······xxx······時(shí)間戳4·······1·······xxx······時(shí)間戳5·······1·······xxx······時(shí)間戳6·······3·······xxx······時(shí)間戳

現(xiàn)在想 select * from B left join A on B.cateid = A.id，當(dāng)然這樣會有重復(fù)的分類文章會查出來。怎么寫SQL 能從B 表關(guān)聯(lián)查詢出每個(gè)分類下的最新發(fā)表的文章和對應(yīng)的文章分類呢？求 具體的查詢語句

問題解答

你得先分組查詢出每個(gè)分類下時(shí)間戳最大的文章然后再去和A聯(lián)合查詢

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