假设有一张新闻分类表和一张新闻表,查询结果要:显示所有新闻分类且关联出每个分类下的最新的一条新闻。
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
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