sql取第一个值 - sql查询最新一条数据




获得每个组的前1行 (10)

从上面验证克林特真棒和正确的答案:

下面两个查询之间的表现很有趣。 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子查询,因为我遇到过这种情况,导致了一些可怕的执行计划。 但里程不一。 何时何地需要查看执行计划和配置文件性能!

我有一张表格,我想为每个组获得最新的条目。 这里是表格:

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的值相关联:(


尝试这个:

        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]

我在这里对各种建议做了一些计时,结果实际上取决于涉及的表的大小,但最一致的解决方案是使用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解决方案(大约一半执行时间)的巨大性能提升,因为这将提供相同的排序顺序,并且是更快的排序方式。


这是关于这个话题最容易找到的问题之一,所以我想给它一个现代的答案(既供我参考,也帮助其他人)。 通过使用over和first值,您可以简化上述查询的工作:

select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

这应该在sql server 2008中运行起来。 当使用over子句时,可以将第一个值视为完成select top 1的一种方法。 Over允许在选择列表中进行分组,而不是编写嵌套的子查询(就像现有的许多答案一样),这样可以更加可读。 希望这可以帮助。


;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