sql - T-sql如何获取所有行 'between'另外两行

Question

假设我有下表:

Table: Score
field: ID: uniqueidentifier
field: Departmentid: int
field: Score: float
field: EnteredOn: DateTime

我如何设计一个查询，为我提供部门 115 和部门 119 之间的所有分数？

澄清一下:如果我有以下记录:

Id, departmentid, score
<some guid>, 115, 1
<some guid>, 100, 2
<some guid>, 119, 3
<some guid>, 115, 2
<some guid>, 102, 1
<some guid>, 119, 4
<some guid>, 115, 2
<some guid>, 100, 4
<some guid>, 120, 4

查询需要检索以下记录:

<some guid>, 100, 2
<some guid>, 102, 1

因为它们在 115 到 119 条记录之间。

默认情况下，记录将在 EntereOn 上排序。

Answer 1

它不是很漂亮，但它适用于您的示例数据。

declare @Score table
(
  ID int identity primary key,
  DepartmentID int,
  Score int,
  EnteredOn int
)

insert into @Score values
(115, 1, 1),
(100, 2, 2),
(119, 3, 3),
(115, 2, 4),
(102, 1, 5),
(119, 4, 6),
(115, 2, 7),
(100, 4, 8),
(120, 4, 9)

;with C1 as
(
  select *,
         row_number() over(order by EnteredOn) as rn
  from @Score
), C2 as
(
  select rn,
         row_number() over(order by EnteredOn) as rn2
  from C1
  where DepartmentID = 115
), C3 as
(
  select rn,
         row_number() over(order by EnteredOn) as rn2
  from C1
  where DepartmentID = 119 and rn > (select min(rn) from C2)
), C4 as
(
  select C2.rn as FromRn,
         C3.rn as ToRn
  from C2 
    inner join C3
      on C2.rn2 = C3.rn2
)
select C1.ID, C1.DepartmentID, C1.Score
from C1
  inner join C4
    on C1.rn > C4.FromRn and
       C1.rn < C4.ToRn