one - t sql group by select top 1




獲得每個組的前1行 (10)

嘗試這個:

        SELECT [DocumentID], 
        [tmpRez].value('/x[2]','varchar(20)') as [Status],
 [tmpRez].value('/x[3]','datetime') as [DateCreated] 
FROM (
        SELECT [DocumentID],
    cast('<x>'+max(cast([ID] as varchar(10))+'</x><x>'+[Status]+'</x><x>'
    +cast([DateCreated] as varchar(20)))+'</x>' as XML) as [tmpRez]
        FROM DocumentStatusLogs
        GROUP by DocumentID) as [tmpQry]

我有一張表格,我想為每個組獲得最新的條目。 這裡是表格:

DocumentStatusLogs

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

該表將按DocumentID進行分組,並按降序排列DateCreated進行排序。 對於每個DocumentID ,我想獲得最新狀態。

我的首選輸出:

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |
  • 是否有任何聚合函數只能獲得每個組的頂部? 請參閱下面的偽代碼GetOnlyTheTop

    SELECT
      DocumentID,
      GetOnlyTheTop(Status),
      GetOnlyTheTop(DateCreated)
    FROM DocumentStatusLogs
    GROUP BY DocumentID
    ORDER BY DateCreated DESC
    
  • 如果這樣的功能不存在,有什麼辦法可以實現我想要的輸出嗎?

  • 或者首先,這可能是由非規範化數據庫引起的嗎? 我在想,因為我正在尋找的只是一行,如果該status也位於父表中?

請參閱父表以獲取更多信息:

當前Documents

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

父表是否應該像這樣,以便我可以輕鬆訪問其狀態?

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

更新我剛剛學會瞭如何使用“應用”,這可以更容易地解決這些問題。


在你想避免使用row_count()的場景中,你也可以使用左連接:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

對於示例模式,您還可以使用“not in subquery”,它通常編譯為與左連接相同的輸出:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

請注意,如果表中沒有至少一個單列唯一鍵/約束/索引(在本例中為主鍵“Id”),則子查詢模式將不起作用。

這兩個查詢往往比row_count()查詢(由查詢分析器衡量)更“昂貴”。 但是,您可能會遇到更快地返回結果或啟用其他優化的情況。


它在SQLite中檢查,你可以使用以下簡單的查詢與GROUP BY

SELECT MAX(DateCreated), *
FROM DocumentStatusLogs
GROUP BY DocumentID

MAX有助於從每個組獲得最大DateCreated

但似乎MYSQL不會將*列與max DateCreated的值相關聯:(


從上面驗證克林特真棒和正確的答案:

下面兩個查詢之間的表現很有趣。 52%是第一名。 第二個是48%。 使用DISTINCT而不是ORDER BY,性能提高4%。 但ORDER BY的優點是可以按多列排序。

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END

CREATE TABLE #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Status] varchar(20),
    [DateCreated] datetime
)

INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

選項1:

    SELECT
    [Extent1].[ID], 
    [Extent1].[DocumentID],
    [Extent1].[Status], 
    [Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
    OUTER APPLY (
        SELECT TOP 1
            [Extent2].[ID], 
            [Extent2].[DocumentID],
            [Extent2].[Status], 
            [Extent2].[DateCreated]
        FROM #DocumentStatusLogs AS [Extent2]
        WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
        ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
    ) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

選項2:

SELECT 
    [Limit1].[DocumentID] AS [ID], 
    [Limit1].[DocumentID] AS [DocumentID], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[DateCreated] AS [DateCreated]
FROM (
    SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
    OUTER APPLY  (
        SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
        FROM (
            SELECT 
                [Extent2].[ID] AS [ID], 
                [Extent2].[DocumentID] AS [DocumentID], 
                [Extent2].[Status] AS [Status], 
                [Extent2].[DateCreated] AS [DateCreated]
            FROM #DocumentStatusLogs AS [Extent2]
            WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
        )  AS [Project2]
        ORDER BY [Project2].[ID] DESC
    ) AS [Limit1]

M $的管理工作室:突出顯示並運行第一個區塊後,突出顯示選項1和選項2,右鍵單擊 - > [顯示預計執行計劃]。 然後運行整個事情來查看結果。

選項1結果:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

選項2結果:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

注意:

當我想要加入1到1(許多中的1個)時,我傾向於使用APPLY。

如果我想讓連接為1對多或多對多,我使用JOIN。

我用ROW_NUMBER()避免了CTE,除非我需要做一些先進的工作,並且可以滿足窗口性能的要求。

我也避免了WHERE或ON子句中的EXISTS / IN子查詢,因為我遇到過這種情況,導致了一些可怕的執行計劃。 但里程不一。 何時何地需要查看執行計劃和配置文件性能!


我在這裡對各種建議做了一些計時,結果實際上取決於涉及的表的大小,但最一致的解決方案是使用CROSS APPLY這些測試是針對SQL Server 2008-R2運行的, 6,500條記錄,另一條(相同的模式),有1.37億條記錄。 被查詢的列是表中主鍵的一部分,表寬度非常小(約30個字節)。 SQL Server從實際執行計劃中報告時間。

Query                                  Time for 6500 (ms)    Time for 137M(ms)

CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

我認為真正令人驚訝的是,無論涉及多少行,交叉應用的時間是多麼一致。


我的代碼從每個組中選擇top 1

select a.* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)

這是一個相當古老的線索,但我認為我會把我的兩分錢放在一起,因為接受的答案對我來說效果不佳。 我在一個大數據集上嘗試了gbn的解決方案,發現它非常慢(SQL Server 2012中超過500萬條記錄的時間超過45秒)。 看一下執行計劃,很明顯問題是它需要一個SORT操作,這會顯著降低速度。

以下是我從不需要SORT操作的實體框架中解脫出來的替代方法,並執行非聚集索引搜索。 這將上述記錄集的執行時間減少到<2秒。

SELECT 
[Limit1].[DocumentID] AS [DocumentID], 
[Limit1].[Status] AS [Status], 
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
    FROM (SELECT 
        [Extent2].[ID] AS [ID], 
        [Extent2].[DocumentID] AS [DocumentID], 
        [Extent2].[Status] AS [Status], 
        [Extent2].[DateCreated] AS [DateCreated]
        FROM [dbo].[DocumentStatusLogs] AS [Extent2]
        WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
    )  AS [Project2]
    ORDER BY [Project2].[ID] DESC) AS [Limit1]

現在我假定原始問題中沒有完全指定的東西,但是如果您的表格設計是這樣的,即您的ID列是自動增量ID,並且每個插入點將DateCreated設置為當前日期,那麼偶數如果沒有使用上面的查詢運行,您實際上可以通過對ID進行排序而不是在DateCreated上進行排序,從而獲得對gbn解決方案(大約一半執行時間)的巨大性能提升,因為這將提供相同的排序順序,並且是更快的排序方式。


這是我能想到的最香的TSQL

    SELECT * FROM DocumentStatusLogs D1 JOIN
    (
      SELECT
        DocumentID,MAX(DateCreated) AS MaxDate
      FROM
        DocumentStatusLogs
      GROUP BY
        DocumentID
    ) D2
    ON
      D2.DocumentID=D1.DocumentID
    AND
      D2.MaxDate=D1.DateCreated

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

如果您期望每天有2個參賽作品,那麼這將隨意挑選一個參賽作品。 要獲得一天的兩個條目,請改用DENSE_RANK

至於規範化與否,這取決於你是否想要:

  • 保持2個地方的狀態
  • 保存狀態歷史
  • ...

就目前而言,你保存了狀態歷史記錄。 如果你想在父表中獲得最新的狀態(這是非規範化),你需要一個觸發器來維護父狀態。 或者丟棄這個狀態歷史表。


SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

什麼數據庫服務器 此代碼不適用於所有這些代碼。

關於你的問題的後半部分,我認為列入狀態似乎是合理的。 您可以將DocumentStatusLogs作為日誌保留,但仍將最新信息存儲在主表中。

順便說一句,如果你已經在Documents表中有DateCreated列,那麼你可以使用它加入DocumentStatusLogs (只要DateCreatedDocumentStatusLogs是唯一的)。

編輯:MsSQL不支持USING,所以將其更改為:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated




greatest-n-per-group