<small id='Wyynk'></small><noframes id='Wyynk'>

  • <i id='Wyynk'><tr id='Wyynk'><dt id='Wyynk'><q id='Wyynk'><span id='Wyynk'><b id='Wyynk'><form id='Wyynk'><ins id='Wyynk'></ins><ul id='Wyynk'></ul><sub id='Wyynk'></sub></form><legend id='Wyynk'></legend><bdo id='Wyynk'><pre id='Wyynk'><center id='Wyynk'></center></pre></bdo></b><th id='Wyynk'></th></span></q></dt></tr></i><div id='Wyynk'><tfoot id='Wyynk'></tfoot><dl id='Wyynk'><fieldset id='Wyynk'></fieldset></dl></div>
      <legend id='Wyynk'><style id='Wyynk'><dir id='Wyynk'><q id='Wyynk'></q></dir></style></legend>

          <bdo id='Wyynk'></bdo><ul id='Wyynk'></ul>

        <tfoot id='Wyynk'></tfoot>

        获取每组的前 1 行

        Get top 1 row of each group(获取每组的前 1 行)
        <i id='eBmD1'><tr id='eBmD1'><dt id='eBmD1'><q id='eBmD1'><span id='eBmD1'><b id='eBmD1'><form id='eBmD1'><ins id='eBmD1'></ins><ul id='eBmD1'></ul><sub id='eBmD1'></sub></form><legend id='eBmD1'></legend><bdo id='eBmD1'><pre id='eBmD1'><center id='eBmD1'></center></pre></bdo></b><th id='eBmD1'></th></span></q></dt></tr></i><div id='eBmD1'><tfoot id='eBmD1'></tfoot><dl id='eBmD1'><fieldset id='eBmD1'></fieldset></dl></div>
          <bdo id='eBmD1'></bdo><ul id='eBmD1'></ul>
            <legend id='eBmD1'><style id='eBmD1'><dir id='eBmD1'><q id='eBmD1'></q></dir></style></legend>

              <tfoot id='eBmD1'></tfoot>

            1. <small id='eBmD1'></small><noframes id='eBmD1'>

                    <tbody id='eBmD1'></tbody>
                1. 本文介绍了获取每组的前 1 行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有一张表,我想获取每个组的最新条目.这是表:

                  I have a table which I want to get the latest entry for each group. Here's the table:

                  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,我想获得最新状态.

                  The table will be grouped by DocumentID and sorted by DateCreated in descending order. For each DocumentID, I want to get the latest status.

                  我的首选输出:

                  | 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
                    

                  • 如果没有这样的功能,有什么办法可以实现我想要的输出吗?

                  • If such function doesn't exist, is there any way I can achieve the output I want?

                    有关详细信息,请参阅父表:

                    Please see the parent table for more information:

                    Current Documents 表格

                    Current Documents Table

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

                    父表是否应该是这样,以便我可以轻松访问其状态?

                    Should the parent table be like this so that I can easily access its status?

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

                    更新我刚刚学会了如何使用应用",它可以更轻松地解决此类问题.

                    UPDATE I just learned how to use "apply" which makes it easier to address such problems.

                    推荐答案

                    ;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

                    If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

                    归一化与否,看你愿不愿意:

                    As for normalised or not, it depends if you want to:

                    • 在 2 个地方保持状态
                    • 保留状态历史
                    • ...

                    就目前而言,您可以保留状态历史记录.如果您也想要父表中的最新状态(这是非规范化),您需要一个触发器来维护父表中的状态".或删除此状态历史记录表.

                    As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

                    这篇关于获取每组的前 1 行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                    本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Select n random rows from SQL Server table(从 SQL Server 表中随机选择 n 行)
                  SQL query to select dates between two dates(用于选择两个日期之间的日期的 SQL 查询)
                  How can I delete using INNER JOIN with SQL Server?(如何在 SQL Server 中使用 INNER JOIN 进行删除?)
                  Table Naming Dilemma: Singular vs. Plural Names(表命名困境:单数与复数名称)
                  INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server(INSERT 语句与 FOREIGN KEY 约束冲突 - SQL Server)
                  How ROWNUM works in pagination query?(ROWNUM 如何在分页查询中工作?)

                        <tbody id='EBqa8'></tbody>

                      <i id='EBqa8'><tr id='EBqa8'><dt id='EBqa8'><q id='EBqa8'><span id='EBqa8'><b id='EBqa8'><form id='EBqa8'><ins id='EBqa8'></ins><ul id='EBqa8'></ul><sub id='EBqa8'></sub></form><legend id='EBqa8'></legend><bdo id='EBqa8'><pre id='EBqa8'><center id='EBqa8'></center></pre></bdo></b><th id='EBqa8'></th></span></q></dt></tr></i><div id='EBqa8'><tfoot id='EBqa8'></tfoot><dl id='EBqa8'><fieldset id='EBqa8'></fieldset></dl></div>
                    • <small id='EBqa8'></small><noframes id='EBqa8'>

                    • <tfoot id='EBqa8'></tfoot>

                        • <bdo id='EBqa8'></bdo><ul id='EBqa8'></ul>

                          • <legend id='EBqa8'><style id='EBqa8'><dir id='EBqa8'><q id='EBqa8'></q></dir></style></legend>