有子查詢時如何使用 COUNT (OVER?) 進行 OFFSET & LIMIT？

看來我有一個我無法完全解決的案例。因此，來到這里希望找到指向可能對其他人也有幫助的查詢的指針。

在下文中，我有一個查詢，就返回結果而言，它執行正常，但需要第二個查詢，該查詢與此處提供的查詢相同，但沒有OFFSET，輸出只是COUNT(*)所有行中的一個。

我有兩個目標：

1. 編寫查詢，以便COUNT(*)在同一查詢中返回。事實上，我一直在尋找幫助文章，例如優秀的SQL SERVER – How to get total row count from OFFSET / FETCH NEXT (Paging)用不同的方法解決問題，但是還有另一篇文章……
2. 使用視窗函式（例如OVER(PARTITION BY）或一些更高效的方式重寫連接，因為該查詢似乎會在表上生成一個INDEX SCAN和INDEX SEEK。真正的查詢在該部分中要復雜一些WHERE，但在我看來，如果查詢更簡單一些，那麼即使一次掃描也足夠了，這樣COUNTandMAX就可以與外部查詢同時進行。即使這將是一場胜利，但結合整體COUNT將更大。

也許我想咀嚼的東西比我現在能咀嚼的多一點，但另一方面，也許現在有機會學習一些東西。

這是表格和數據

CREATE TABLE Temp
(
   Id INT NOT NULL PRIMARY KEY,
   Created INT NOT NULL,
   ParentId INT,
   SomeInfo INT NOT NULL,
   GroupId INT NOT NULL

   CONSTRAINT FK_Temp FOREIGN KEY(ParentId) REFERENCES Temp(Id)
);

-- Some root levels nodes.
INSERT INTO Temp VALUES(1, 1, NULL, 1, 1);
INSERT INTO Temp VALUES(2, 2, NULL, 2, 2);
INSERT INTO Temp VALUES(3, 3, NULL, 1, 3);
INSERT INTO Temp VALUES(13, 13, NULL, 1, 1);

-- First order child nodes.
INSERT INTO Temp VALUES(4, 4, 1, 2, 1);
INSERT INTO Temp VALUES(5, 5, 2, 1, 2);
INSERT INTO Temp VALUES(6, 6, 3, 2, 3);

-- Second order child nodes.
INSERT INTO Temp VALUES(7, 7, 4, 1, 1);
INSERT INTO Temp VALUES(8, 8, 5, 2, 2);
INSERT INTO Temp VALUES(9, 9, 6, 1, 3);

SELECT
   Id,
   newestTable.SomeInfo,
   newestTable.Created,
   CASE WHEN newestTable.RootCount > 1 THEN 1 ELSE 0 END AS IsMulti
FROM
  Temp as originalTable
  INNER JOIN
  (
       SELECT
           SomeInfo,
           Max(Created) AS Created,
           Count(*) AS RootCount
       FROM
           Temp
       WHERE ParentId IS NULL AND GroupId = 1
       GROUP BY SomeInfo
   ) AS newestTable ON originalTable.SomeInfo = newestTable.SomeInfo AND originalTable.Created = newestTable.Created
/*WHERE
(
   originalTable.SomeInfo = 1
)*/
ORDER BY newestTable.Created ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

PS 另外，如何在子查詢中應用外部限制偏移和過濾器以避免對 Postgresql 中子查詢中使用的完整表進行分組看起來很有趣。

<編輯：

看起來像

SELECT
   Id,
   SomeInfo,
   GroupId,
   ParentId,
   MAX(Created) OVER(PARTITION BY SomeInfo) AS Created,
   COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
   CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) > 1 THEN 1 ELSE 0 END AS IsMulti
FROM
   Temp
WHERE
(
   GroupId = 1 AND ParentId IS NULL
)
ORDER BY Created ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

接近那裡。但是，問題是，現在有兩個結果行，在我看來，這是由於原始INNER JOIN加入到Temp該行中將其剔除。我想知道是否有辦法在視窗之前或之後以某種方式應用條件以更接近地匹配原始查詢。（要清楚，這不是同一個查詢。數據太少，因此查詢看起來彼此接近。）

因此，在我看來，您缺少的是“僅返回Created每個實例的最高記錄”。所以你得到了所有的行，然後它的最高Created值是相同的SomeInfo記錄。不幸的是，您不能只將 the 添加MAX(Created) = Created到基本WHERE子句中。

如果您只是將整個東西包裝在 aCTE中，那麼您只需將 a 添加MAX(Created) = Created到中WHERE並獲得您正在尋找的東西（不是我認為CTE’s 是所有東西的答案）。

WITH CTE (ID, SomeInfo, GroupID, ParentID, Created, MaxCreated, RootCount, IsMulti)
AS
(
   SELECT
       Id,
       SomeInfo,
       GroupId,
       ParentId,
       Created,
       MAX(Created) OVER(PARTITION BY SomeInfo) AS MaxCreated,
       COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
       CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) > 1 THEN 1 ELSE 0 END AS IsMulti
   FROM
       Temp
)
SELECT ID, SomeInfo, GroupID, ParentID,  MaxCreated AS Created, RootCount, IsMulti
FROM CTE
WHERE
(
   GroupId = 1 
   AND ParentId IS NULL
   AND Created = MaxCreated
)
ORDER BY MaxCreated ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

在我的快速測試中，它具有相同的執行計劃並且不需要任何額外的執行時間（參見下面添加的執行計劃）。（現在它是一個小的結果集，因此您可能仍需要對其進行測試。）

希望那是您正在尋找的更多內容。

引用自：https://dba.stackexchange.com/questions/240326