SQL 查询返回具有空字段的前 n 条记录或按该字段分组的记录

Question

我有一个表 A，其中有一个过滤列

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |
| 4  |  frank | null   |
| 5  |  sarah |  b     |
| 6  |  jamie |  b     |

假设记录按 id 排序。过滤器值相同的记录应只计为一条。

TOP(1) 应该返回

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |

TOP(2) 应该返回

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |

TOP(3) 应该返回

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |
| 4  |  frank | null   |

TOP(4) 应该返回

| id |  name  | filter |
| 1  |  joe   |  a     |
| 2  |  anna  |  a     |
| 3  |  mike  | null   |
| 4  |  frank | null   |
| 5  |  sarah |  b     |
| 6  |  jamie |  b     |

Answer 1

您可以使用窗口化的 MIN() 对具有相同过滤器的值(以及不同组中的 NULL 值)进行分组，然后使用 DENSE_RANK() 来展平值，以便稍后可以对其进行过滤。

IF OBJECT_ID('tempdb..#Values') IS NOT NULL
    DROP TABLE #Values

CREATE TABLE #Values (
    ID INT IDENTITY,
    Name VARCHAR(10),
    Filter VARCHAR(10))

INSERT INTO #Values (
    Name,
    Filter)
VALUES
    ('joe', 'a'),
    ('anna', 'a'),
    ('mike', NULL),
    ('frank', NULL),
    ('sarah', 'b'),
    ('jamie', 'b'),
    ('john', 'a')

DECLARE @v_TopFilter INT = 4 -- Your top filter here

;WITH MinimumByFilter AS
(
    SELECT
        V.*,
        MinimumIDByFilter = MIN(V.ID) OVER (
            PARTITION BY 
                V.Filter,
                CASE WHEN V.Filter IS NULL THEN V.ID END)
    FROM
        #Values AS V
),
DenseRank AS
(
    SELECT
        M.*,
        DenseRank = DENSE_RANK() OVER(ORDER BY M.MinimumIDByFilter ASC)
    FROM
        MinimumByFilter AS M
)
SELECT
    D.ID,
    D.Name,
    D.Filter
FROM
    DenseRank AS D
WHERE
    D.DenseRank <= @v_TopFilter
ORDER BY
    D.ID ASC

您可以在此处检查函数返回的内容:

ID  Name    Filter  MinimumIDByFilter   DenseRank
1   joe     a       1                   1
2   anna    a       1                   1
7   john    a       1                   1
3   mike    NULL    3                   2
4   frank   NULL    4                   3
5   sarah   b       5                   4
6   jamie   b       5                   4