sql-server - 如何分组并选择 1 行，或者如果超过 1 行，则仅使用 SQL SELECT 的最后一行

Question

我有这个在 SQL Server 2014 存储过程中运行的 SELECT。

SELECT   AdminTest.Title       AS Title,
         COUNT(AdminTestQuestion.AdminTestQuestionId) AS Q,
         UserTest.UserTestId AS UId,
         UserTest.TestStatusId AS Status,
         UserTest.Sequence As Sequence
FROM     AdminTest 
JOIN     UserTest
ON       AdminTest.AdminTestId = UserTest.AdminTestId 
AND      UserTest.UserId = @UserId
JOIN     AdminTestQuestion
ON       AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
GROUP BY AdminTest.Title, 
         UserTest.TestStatusId, 
         UserTest.UserTestId,
         UserTest.Sequence

这是上面的选择当前返回的数据:

Title    Q      UId     Status   Sequence
TestA    25     null    0        1
TestA    25     1235    2        2
TestB    10     null    0        1
TestB    10     1237    2        2
TestB    10     1238    2        3
TestC    10     null    0        1

这里是我想要获取的数据:

Title    Q      UId     Status   Sequence
TestA    25     1235    2        2 
TestB    10     1237    2        2 
TestB    10     1238    2        3
TestC    10     null    0        1

我坚持的是我需要:

• 如果测试只有一行，我需要显示该行
• 如果测试有多行，我需要显示除第一行以外的每一行(按序号排序)。

我知道这很令人困惑，我认为仅使用 SELECT 是不可能的。

如果有任何建议，我将不胜感激。如果需要，我可以在 SELECT 之后使用存储过程并使用 CURSOR，但我真的不确定从哪里开始使用 CURSORS。

希望有人指教。

Answer 1

根据您的评论，您可以尝试这样做:

查询采用“Seq”大于 1 的所有或“Seq”为 1，但前提是没有更高的“Seq”:

;WITH MyQuery AS
(
    SELECT   AdminTest.Title       AS Title,
             COUNT(AdminTestQuestion.AdminTestQuestionId) AS Questions,
             UserTest.UserTestId AS UserTestId,
             UserTest.TestStatusId AS UserTestStatusId,
             UserTest.Sequence As Seq
    FROM     AdminTest 
    JOIN     UserTest
    ON       AdminTest.AdminTestId = UserTest.AdminTestId 
    AND      UserTest.UserId = @UserId
    JOIN     AdminTestQuestion
    ON       AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
    GROUP BY AdminTest.Title, 
             UserTest.TestStatusId, 
             UserTest.UserTestId,
             UserTest.Sequence
)
SELECT *
FROM MyQuery
WHERE Seq>1
   OR (Seq=1 
       AND NOT EXISTS(SELECT 1 
                      FROM MyQuery AS innerQuery 
                      WHERE innerQuery.Title=MyQuery.Title
                        AND innerQuery.Seq>1)
      );