如何在sql中使用别名与分区

Question

我正在记录那些被归类为高尔夫障碍的球员。就像0到5之间有障碍的球员一样，在0-5的范围内也有类似的障碍，6-11之间的障碍也在6-11之间，依此类推。我正在尝试的是从每个射程中获取前3名球员，这样我就可以为每一轮设置飞行。

我已经使用分区按子句分离记录和ROW_NUMBER从每个范围获得前3名球员。为了定义范围，我使用了多个案例。现在，如何使用range作为带分区的别名，或者以任何方式生成正确的结果。下面是我的问题。

  select * from (
  select  uu.Id, firstname, lastname, userhandicap, 
  case when userhandicap>=0 and userhandicap<=5 then '0-5'
  when userhandicap>=6 and userhandicap<=11 then '6-11' 
  when UserHandicap>=12 and UserHandicap<=18 then '12-18' 
  when UserHandicap>=19 and UserHandicap<=26 then '19-26'
  else '27 and above' end as range, RN = ROW_Number()  over (PARTITION BY 
  range order by cast(userhandicap as int))
  from dbo.[User] uu inner join dbo.[EventRegisteredUsers] eru
  on uu.Id = eru.UserId 
  where eru.UserId not in (Select fp.UserId from dbo.[FlightPlayer] fp 
  inner join dbo.[Flight] f
  on fp.FlightId = f.Id 
  where f.Rounds = '1'
  and f.Starthole = '0a9b926e-0baa-4369-8cf8-8fc84ca80d65' and f.EventId = 
  '7de10ad6-098d-419f-9c2d-2e62803ad1f7')
  and eru.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7') uu
  WHERE 
   uu.RN <= 3 

Answer 1

可以使用apply在子查询中定义range值。这是定义范围的最简单方法：

select *
from (select uu.Id, firstname, lastname, userhandicap, 
             row_number() over (partition by v.range order by cast(userhandicap as int)) as seqnum
      from dbo.[User] uu inner join
           dbo.[EventRegisteredUsers] eru
           on uu.Id = eru.UserId cross apply
           (values (case when userhandicap <= 5 then '0-5'
                         when userhandicap <= 11 then '6-11' 
                         when UserHandicap <= 18 then '12-18' 
                         when UserHandicap <= 26 then '19-26'
                         else '27 and above'
                    end)
           ) v(range)
      where not exists (select 1
                        from dbo.[FlightPlayer] fp join
                             dbo.[Flight] f
                             on fp.FlightId = f.Id 
                        where eru.UserId = fp.UserId and 
                              f.Rounds = '1' and
                              f.Starthole = '0a9b926e-0baa-4369-8cf8-8fc84ca80d65' and
                              f.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7'
                       ) and
            eru.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7'
       ) uu
where uu.seqnum <= 3; 

注意查询的其他更改：

• 不要在子查询中使用not in。如果子查询返回一个NULL值，那么所有的值都会被过滤掉。这(通常)不是预期的行为。
• case表达式过于复杂。使用保证case按顺序评估条件的事实。
• 应该限定具有多个查询的查询中的所有列名。然而，目前尚不清楚这些专栏从何而来。
• 根据你最初的逻辑(以及高尔夫的规则)，handicap大概从来都不是负面的，所以我很乐意去除这个条件。

Answer 2

使用cte

with cte as
(
select  uu.Id, firstname, lastname, userhandicap, 
  case when userhandicap>=0 and userhandicap<=5 then '0-5'
  when userhandicap>=6 and userhandicap<=11 then '6-11' 
  when UserHandicap>=12 and UserHandicap<=18 then '12-18' 
  when UserHandicap>=19 and UserHandicap<=26 then '19-26'
  else '27 and above' end as range
  from dbo.[User] uu inner join dbo.[EventRegisteredUsers] eru
  on uu.Id = eru.UserId 
  where eru.UserId not in (Select fp.UserId from dbo.[FlightPlayer] fp 
  inner join dbo.[Flight] f
  on fp.FlightId = f.Id 
  where f.Rounds = '1'
  and f.Starthole = '0a9b926e-0baa-4369-8cf8-8fc84ca80d65' and f.EventId = 
  '7de10ad6-098d-419f-9c2d-2e62803ad1f7')
  and eru.EventId = '7de10ad6-098d-419f-9c2d-2e62803ad1f7'
), t2 as 
(
 select *,row_number() over(partition by range order by cast(userhandicap as int) rn from cte
) select * from t2 where rn<=3