在 SQL Server 中根据用户条件显示表记录

解决此问题的一种方法是从Enrollment表格开始。使用ROW_NUMBER排名功能，您可以按以下组分配行号SemesterID：

SELECT
  *,
  RN = ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC)
FROM
  dbo.Enrollment
;

对于您的示例，您将获得如下输出：

Id  StudentID  SemesterID  RN
--  ---------  ----------  --
1   1          1           1
2   2          2           1
3   3          2           2
4   4          1           2
5   5          2           3
6   6          2           4
7   7          1           3
8   8          2           5
9   9          1           4
10  10         1           5
11  11         2           6
12  12         1           6

现在减去一个RN，然后将结果除以所需的学生人数。如果学生人数是3，那么你会发现前面的计算变成了一个序列

1, 2, 3, 4, 5, 6, ...

进入

0, 0, 0, 1, 1, 1, ...

也就是说，你会得到一个由多组重复数字组成的序列，每组三个（因为我们除以三）。您可以将该结果用作主要排序标准。

选择SemesterID作为您的第二个标准和Id或StudentID作为第三个标准，所以现在查询将如下所示：

SELECT
  *,
  GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
FROM
  dbo.Enrollment
ORDER BY
  GroupID ASC,
  SemesterID ASC,
  Id ASC
;

这将是基于提供的样本的输出：

Id  StudentID  SemesterID  GroupID
--  ---------  ----------  -------
1   1          1           0
4   4          1           0
7   7          1           0
2   2          2           0
3   3          2           0
5   5          2           0
9   9          1           1
10  10         1           1
12  12         1           1
6   6          2           1
8   8          2           1
11  11         2           1

您可以看到行的顺序已经与预期的输出相匹配，您只需将StudentIDand替换SemesterID为它们的描述并去掉GroupID. 将上述查询声明为派生表或 CTE，然后将表连接Student到Semester它。对于我的回答，我选择了 CTE 选项，这就是我得到的：

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student AS st ON rd.StudentID = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

输出：

Id  StudentName Semester
--  ----------- --------
1   A           First
4   D           First
7   G           First
2   B           Second
3   C           Second
5   E           Second
9   I           First
10  J           First
12  L           First
6   F           Second
8   H           Second
11  K           Second

如您所见，到目前为止，我们的查询非常适合提供的示例：结果现在与您的预期输出精确匹配。但是查询还没有完成：缺少学期的过滤器。

假设学期将以逗号分隔的名称/描述列表的形式提供，您可以做的是将该列表转换为行集，每行有一个名称/描述。然后，您可以将该行集与Semester表进行匹配，以仅包含选定的学期。出于此答案的目的，我假设您使用的是 SQL Server 2016 或更高版本，以便可以使用内置SPLIT_STRING函数来帮助实现学期过滤器：

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT('First,Second', ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

在 SQL Server 的早期版本中，您必须提供自己的方法将字符串列表转换为子字符串的行集。值得庆幸的是，互联网上的解决方案比比皆是，包括这里和 Stack Overflow 上专门针对这个问题的许多问题和答案。Aaron Bertrand在他的博客文章中讨论了各种方法，以正确的方式拆分字符串 - 或次佳方式，这可能是一个很好的起点。

至于我们的查询，还有最后一步要做：参数化。在上述查询中，学生人数和所选学期列表都是硬编码的。最终解决方案将需要使用参数引用而不是硬编码值，如下所示：

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / @NumberOfStudents
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT(@SemesterList, ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

此解决方案的现场演示可在db<>fiddle.uk 获得。

_{披露：这个答案部分借用了Josh Darnell现在删除的答案。}