查询文章表中每个栏目下最新的2篇文章
select a.id,a.title,a.columnid from news a where a.id in (select top 2 id from news where columnid = a.columnid order by pubdate desc) order by a.columnid asc
这样是可以的
可是如果是这样
select a.id,a.title,a.columnid from news a left join (select top 2 id from news where columnid = a.columnid order by pubdate desc) b on a.id = b.id order by a.columnid asc
就报“列前缀 'a' 与查询中所用的表名或别名不匹配。”
请问这是为什么?
select a.id,a.title,a.columnid from news a where a.id in (select top 2 id from news where columnid = a.columnid order by pubdate desc) order by a.columnid asc
这样是可以的
可是如果是这样
select a.id,a.title,a.columnid from news a left join (select top 2 id from news where columnid = a.columnid order by pubdate desc) b on a.id = b.id order by a.columnid asc
就报“列前缀 'a' 与查询中所用的表名或别名不匹配。”
请问这是为什么?
cross apply/outer apply
where a.id = some (select top 2 id from news where columnid=a.columnid order by pubdate desc)) as b
兄弟,你有没有注意 some 和 in 是等价的