SQL:返回少于 10 个值的 TOP 10 列表中的 NULL 占位符值

Question

我编写了一个查询，返回前 10 个值的结果集。这很好用，但是如果结果集返回小于 10 个项目的值列表，那么我希望查询将占位符值返回到 pas 结果集为 10 个值，即 NULLS。

当前结果集：

SENDBottom10Prog    CFC 11  Business    Surname1    Forename1   11MO    50      2   4   U   -4.00
SENDBottom10Prog    CIN 11  Business    Surname2    Forename2   11MO    86.9    0   4   2   -2.00
SENDBottom10Prog    N   11  Business    Surname3    Forename3   11MC    100     0   3   2   -1.00
SENDBottom10Prog    N   11  Business    Surname4    Forename4   11MO    97.62   5   4   6   2.00

理想的结果集：

SENDBottom10Prog    CFC   11    Business    Surname1    Forename1   11MO    50      2    4    U     -4.00
SENDBottom10Prog    CIN   11    Business    Surname2    Forename2   11MO    86.9    0    4    2     -2.00
SENDBottom10Prog    N     11    Business    Surname3    Forename3   11MC    100     0    3    2     -1.00
SENDBottom10Prog    N     11    Business    Surname4    Forename4   11MO    97.62   5    4    6      2.00
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL
NULL                NULL  NULL  NULL        NULL        NULL        NULL    NULL    NULL NULL NULL   NULL

这是我到目前为止的代码：

DECLARE
@AcademicYear varchar(9) = '2019/2020',
@Collection varchar(50) = 'autumn',
@StuYear       VARCHAR(2) = '11';
Select 'SENDBottom10Prog' as List, SG, Year, Subject, Surname, Forename, Form, [AM/PM], Behaviour, FFT20, Prediction, Residual
From (
SELECT s.sg, s.Year,r.Subject as 'Subject', s.Surname, s.Forename, s.Form,s.attendance as 'AM/PM', s.behaviour, r.Target as FFT20, r.Prediction, g3.Points-g2.Points as Residual, g3.graderank, row_number() over(partition by r.subject order by s.year, r.subject, cast(g3.Points-g2.Points as decimal) asc, g3.graderank asc, Attendance asc, Behaviour, s.Surname, s.Forename) as rn
FROM Results r
LEFT JOIN Grades g1
ON r.Result = g1.Grade
LEFT JOIN Grades g2
ON r.Target = g2.Grade
LEFT JOIN Grades g3
ON r.Prediction = g3.Grade
LEFT JOIN students s
ON r.UPN = s.UPN
WHERE r.AcademicYear = @AcademicYear
AND s.AcademicYear = @AcademicYear
AND r.Collection = @Collection
AND s.year=@StuYear
AND SEND='Y'
AND s.NotIncluded = 'N'

) as T
where T.rn <=10;

Answer 1

如果你想得到十行，但没有它们，那么你需要构造它们。 这是一种方法：

with t as (
< your query here >
)
select t.*
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
) v(n) left join
t
on t.rn = v.n
order by v.n;