如何基于ConnectionId和FileCreated双列整理SQL查询结果？

先明确我们的核心需求：从给定表中返回数据，要满足两个关键逻辑：

• 不能连续返回同一个ConnectionId的记录（实现不同ConnectionId交替出现）
• 整体尽可能遵循FileCreated的升序，优先选择更早创建的记录，除非它的ConnectionId和上一条重复

表结构与示例数据 #

我们的表结构定义如下：

CREATE TABLE YourTable (
    Id int,
    ConnectionId int,
    File varchar(50),
    FileCreated DateTime
);

填充的示例测试数据：

INSERT INTO YourTable VALUES
(1, 3, 'a.dat', '2018-05-16 13:53:40.008'),
(2, 1, 'b.dat', '2018-05-16 13:53:40.007'),
(3, 3, 'c.dat', '2018-05-16 13:53:40.009'),
(4, 3, 'z.dat', '2018-05-16 13:53:40.002'),
(5, 3, 'h.dat', '2018-05-16 13:53:40.003'),
(6, 2, 'ba.dat', '2018-05-16 13:53:40.004'),
(7, 3, 'zy.dat', '2018-05-16 13:53:40.005'),
(8, 1, 'f.dat', '2018-05-16 13:53:40.001'),
(9, 1, 'cd.dat', '2018-05-16 13:53:40.006');

解决方案思路 #

要实现这个需求，我们可以借助窗口函数给每个ConnectionId下的记录按时间排序并分配组内序号，再结合这个序号和时间来排序：

1. 先按ConnectionId分组，给组内的记录按FileCreated升序分配行号（标记这是该ConnectionId下的第几条记录）
2. 最终排序时，先按组内行号升序（保证每个ConnectionId的第1条先出，然后是第2条，以此类推），再按FileCreated升序（同一行号的记录按时间排序，保证更早的优先）

这个逻辑刚好能匹配预期的交替轮询+时间优先的效果。

实现SQL代码 #

WITH RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ConnectionId ORDER BY FileCreated ASC) AS RowNum
    FROM YourTable
)
SELECT Id, ConnectionId, File, FileCreated
FROM RankedData
ORDER BY RowNum ASC, FileCreated ASC;

验证结果 #

执行上述SQL后，返回的结果和预期完全一致：

8, 1, "f.dat", 2018-05-16 13:53:40.001
4, 3, "z.dat", 2018-05-16 13:53:40.002
6, 2, "ba.dat", 2018-05-16 13:53:40.004
5, 3, "h.dat", 2018-05-16 13:53:40.003
9, 1, "cd.dat", 2018-05-16 13:53:40.006
7, 3, "zy.dat", 2018-05-16 13:53:40.005
2, 1, "b.dat", 2018-05-16 13:53:40.007
1, 3, "a.dat", 2018-05-16 13:53:40.008
3, 3, "c.dat", 2018-05-16 13:53:40.009

逻辑解释 #

• ROW_NUMBER() OVER (PARTITION BY ConnectionId ORDER BY FileCreated ASC)：给每个ConnectionId下的记录按创建时间排序，生成组内的行号。比如ConnectionId=1的三条记录，行号分别是1（f.dat）、2（cd.dat）、3（b.dat）；ConnectionId=3的五条记录行号是1到5，按时间从早到晚排序。
• 最终排序ORDER BY RowNum ASC, FileCreated ASC：先按行号排序，确保每个ConnectionId的第1条记录先被取出，然后是第2条，以此类推；同一行号的记录再按时间排序，保证更早的记录优先出现，完美实现了交替轮询+时间优先的需求。

内容的提问来源于stack exchange，提问作者raddevus