SQL查询所有新闻分类且关联出每个分类下的最新的一条新闻

假设有一张新闻分类表和一张新闻表，查询结果要：显示所有新闻分类且关联出每个分类下的最新的一条新闻。

SELECT a.*, b.* FROM News a
      LEFT JOIN (
            SELECT * FROM (
                   SELECT *, ROW_NUMBER() OVER(PARTITION BY TypeId ORDER BY CreateTime DESC) rn FROM NewsType
             ) t WHERE rn<2
      ) b ON a.TypeId=b.TypeId

---

SELECT a.*,b.EC00301,b.EC00306,b.EC00309,b.EC00310 FROM EC001 a LEFT JOIN 

(

select t.EC00301,t.EC00306,t.EC00309,t.EC00310 from

(select row_number() over (partition by EC00306 order by EC00310 desc) rn,* from EC003) t

where rn < 2

) b

ON a.EC001000=b.EC00306