sql - 相当于交叉应用并选择顶部

Question

我有表格(下面的脚本):

use master
go

--
--
if db_id ( 'CrossApplyDemo' ) is not null
drop database CrossApplyDemo
go
create database CrossApplyDemo
go

use CrossApplyDemo
go

--
if object_id ( 'dbo.Countries', 'U' ) is not null
drop table dbo.Countries
go
create table dbo.Countries ( CountryID int, Country nvarchar(255) )
go

--
insert into dbo.Countries ( CountryID, Country )
values ( 1, N'Russia' ), ( 2, N'USA' ), ( 3, N'Germany' )
     , ( 4, N'France' ), ( 5, N'Italy' ), ( 6, N'Spain' )
go

--
if object_id ( 'dbo.Cities', 'U' ) is not null
drop table dbo.Cities
go
create table dbo.Cities ( CityID int, CountryID int, City nvarchar(255) )
go

--
insert into dbo.Cities ( CityID, CountryID, City )
values ( 1, 1, N'Moscow' ), ( 2, 1, N'St. Petersburg' ), ( 3, 1, N'Yekaterinburg' )
     , ( 4, 1, N'Novosibirsk' ), ( 5, 1, N'Samara' ), ( 6, 2, N'Chicago' )
     , ( 7, 2, N'Washington' ), ( 8, 2, N'Atlanta' ), ( 9, 3, N'Berlin' )
     , ( 10, 3, N'Munich' ), ( 11, 3, N'Hamburg' ), ( 12, 3, N'Bremen' )
     , ( 13, 4, N'Paris' ), ( 14, 4, N'Lyon' ), ( 15, 5, N'Milan' )
go

我选择按国家/地区分组的城市，我可以使用两种方法执行查询:

-- using join:
select *
from CrossApplyDemo.dbo.Countries as countries
inner join CrossApplyDemo.dbo.Cities as cities on cities.CountryID = countries.CountryID
-- using apply
select *
from CrossApplyDemo.dbo.Countries as countries
cross apply (select * from CrossApplyDemo.dbo.Cities as cities where cities.CountryID = countries.CountryID) as c;

没有“交叉应用”的任何查询都可以返回与下面的“应用查询”相同的结果吗？ :

select *
from CrossApplyDemo.dbo.Countries as countries
cross apply (select top(3) * from CrossApplyDemo.dbo.Cities as cities where cities.CountryID = countries.CountryID) as c;

查询:

select top(3) *
from CrossApplyDemo.dbo.Countries as countries
inner join CrossApplyDemo.dbo.Cities as cities on cities.CountryID = countries.CountryID

不工作

Answer 1

是的。

select
    CountryID, Country, CityID, CountryID, City
from
(

select Cities.*,
    Country,
    ROW_NUMBER() over (partition by cities.countryId order by cityid) rn

from 
cities
    inner join countries on cities.CountryID= countries.CountryID
) v
where rn<=3
and CountryID=1