将多个“with cte”查询的结果作为一个结果返回

嗯。…我的第一个想法是 union all :

with cte as (
      select top (500) t.*, row_number() over (order by id desc) as seqnum
      from Ticker t
      where ProductId = 'BTC-USD'
      order by id desc
     )
select 500 as which, max(cte.price) as max_price from cte where seqnum <= 500 union all
select 100 as which, max(cte.price) from cte where seqnum <= 100 union all
select 50 as which, max(cte.price) from cte where seqnum <= 50 union all
select 5 as which, max(cte.price) from cte where seqnum <= 5;

但是,我有另一个想法:

with cte as (
      select top (500) t.*, row_number() over (order by id desc) as seqnum
      from Ticker t
      where ProductId = 'BTC-USD'
      order by id desc
     )
select v.which, x.max_price
from (values (5), (50), (100), (500)) v(which) cross apply
     (select max(price) as max_price from cte where seqnum <= which) x;

当然,CTE中的“500”需要与 v . 你真的可以摆脱 TOP 在CTE中。