• <bdo id='XoLhr'></bdo><ul id='XoLhr'></ul>
  • <tfoot id='XoLhr'></tfoot>

      <legend id='XoLhr'><style id='XoLhr'><dir id='XoLhr'><q id='XoLhr'></q></dir></style></legend>
      1. <small id='XoLhr'></small><noframes id='XoLhr'>

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

        Oracle SQL 查询:根据时间检索每组的最新值

        Oracle SQL query: Retrieve latest values per group based on time(Oracle SQL 查询:根据时间检索每组的最新值)
          <bdo id='Bd3kN'></bdo><ul id='Bd3kN'></ul>

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

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

              • <tfoot id='Bd3kN'></tfoot>
                  <tbody id='Bd3kN'></tbody>

                  本文介绍了Oracle SQL 查询:根据时间检索每组的最新值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我在 Oracle DB 中有下表

                  I have the following table in an Oracle DB

                  id     date              quantity
                  1      2010-01-04 11:00  152
                  2      2010-01-04 11:00  210
                  1      2010-01-04 10:45  132
                  2      2010-01-04 10:45  318
                  4      2010-01-04 10:45  122
                  1      2010-01-04 10:30  1
                  3      2010-01-04 10:30  214
                  2      2010-01-04 10:30  5515
                  4      2010-01-04 10:30  210
                  

                  现在我想检索每个 ID 的最新值(及其时间).示例输出:

                  now I'd like to retrieve the latest value (and its time) per id. Example output:

                  id     date              quantity
                  1      2010-01-04 11:00  152
                  2      2010-01-04 11:00  210
                  3      2010-01-04 10:30  214
                  4      2010-01-04 10:45  122
                  

                  我只是不知道如何将其放入查询中...

                  I just can't figure out how to put that into a query...

                  此外,以下选项也不错:

                  Additionally the following options would be nice:

                  选项 1:查询应该只返回最近 XX 分钟的值.

                  Option 1: the query should only return values that are from the last XX minutes.

                  选项 2:id 应该与来自另一个具有 id 和 idname 的表的文本连接.id 的输出应该是这样的:id-idname(例如 1-testid1).

                  Option 2: the id should be concatenated with text from another table that has id and idname. output for id should then be like: id-idname (eg 1-testid1).

                  非常感谢您的帮助!

                  推荐答案

                  鉴于此数据...

                  SQL> select * from qtys
                    2  /
                  
                          ID TS                      QTY
                  ---------- ---------------- ----------
                           1 2010-01-04 11:00        152
                           2 2010-01-04 11:00        210
                           1 2010-01-04 10:45        132
                           2 2010-01-04 10:45        318
                           4 2010-01-04 10:45        122
                           1 2010-01-04 10:30          1
                           3 2010-01-04 10:30        214
                           2 2010-01-04 10:30       5515
                           4 2010-01-04 10:30        210
                  
                  9 rows selected.
                  
                  SQL>
                  

                  ...下面的查询给出了你想要的...

                  ... the following query gives what you want ...

                  SQL> select x.id
                    2         , x.ts as "DATE"
                    3         , x.qty as "QUANTITY"
                    4  from (
                    5      select id
                    6             , ts
                    7             , rank () over (partition by id order by ts desc) as rnk
                    8             , qty
                    9      from qtys ) x
                   10  where x.rnk = 1
                   11  /
                  
                          ID DATE               QUANTITY
                  ---------- ---------------- ----------
                           1 2010-01-04 11:00        152
                           2 2010-01-04 11:00        210
                           3 2010-01-04 10:30        214
                           4 2010-01-04 10:45        122
                  
                  SQL>
                  

                  关于您的其他要求,您可以对外部 WHERE 子句应用其他过滤器.同样,您可以像连接任何其他表一样将其他表加入内联视图.

                  With regards to your additional requirements, you can apply additional filters to the outer WHERE clause. Similarly you can join additional tables to the inline view like it was any other table.

                  这篇关于Oracle SQL 查询:根据时间检索每组的最新值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

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

                  相关文档推荐

                  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 如何在分页查询中工作?)
                  <i id='F23BZ'><tr id='F23BZ'><dt id='F23BZ'><q id='F23BZ'><span id='F23BZ'><b id='F23BZ'><form id='F23BZ'><ins id='F23BZ'></ins><ul id='F23BZ'></ul><sub id='F23BZ'></sub></form><legend id='F23BZ'></legend><bdo id='F23BZ'><pre id='F23BZ'><center id='F23BZ'></center></pre></bdo></b><th id='F23BZ'></th></span></q></dt></tr></i><div id='F23BZ'><tfoot id='F23BZ'></tfoot><dl id='F23BZ'><fieldset id='F23BZ'></fieldset></dl></div>
                  <legend id='F23BZ'><style id='F23BZ'><dir id='F23BZ'><q id='F23BZ'></q></dir></style></legend>

                        <tbody id='F23BZ'></tbody>

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

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

                            <tfoot id='F23BZ'></tfoot>