• <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 查询:根据时间检索每组的最新值

        时间:2023-07-18
          <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 查询:根据时间检索每组的最新值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:连接/聚合字符串的最佳方式 下一篇: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>

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

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

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