sql - sql如何从一组数据中获取最中间的记录

Question

create table #middle
(
A INT,
B INT,
C INT
)

INSERT INTO #middle (A,B,C) VALUES (7,6,2),(1,0,8),(9,12,16),(7, 16, 2),(1,12,8), (9,12,16),(9,12,16),(7, 16, 2),(1,12,8), (9,12,16) 

    ;WITH MIDS
         AS (SELECT *,
                    Row_number()
                      OVER (
                        ORDER BY a, b, c DESC )AS rn
             FROM   #middle)
    SELECT *
    FROM   MIDS
    WHERE  rn <= (SELECT CASE ( Count(*)%2 )
                           WHEN 0 THEN ( Count(*) / 2 ) + 1
                           ELSE ( Count(*) / 2 )
                         END
                  FROM   MIDS) except (SELECT *
                                  FROM   MIDS
                                  WHERE  rn < (SELECT ( Count(*) / 2 )
                                               FROM   MIDS)) 

我试过的查询工作 >4 条记录但不是 '3'。现在我的问题是我应该如何修改我的查询以便对于 3 条记录我应该得到第二条记录，这是其中最中间的记录，试试从上述记录和帮助中仅插入 3 条记录。提前致谢。

Answer 1

您可以使用 OFFSET 和 FETCH

select *
from #middle
order by a, b, c desc
offset (select count(*) / 2 - (case when count(*) % 2 = 0 then 1 else 0 end) from #middle) rows
fetch next (select 2 - (count(*) % 2) from #middle) rows only