sql - 用于从一张表中获取少量内容的 SQL 查询

Question

我有一张桌子的问题。这是表格的外观：

+----------+------------+-------------+------+
| index_id | version_id |    date     | type |
+----------+------------+-------------+------+
|        1 |          4 | today       | C    |
|        1 |          2 | last month  | C    |
|        1 |          4 | today       | U    |
|        2 |          3 | yesterday   | c    |
|        2 |          4 | today       | C    |
|        2 |          2 | last year   | U    |
|        3 |          7 | yesterday   | C    |
|        3 |          6 | last month  | C    |
|        3 |          8 | today       | U    |
+----------+------------+-------------+------+

我想要实现的是获取具有两种类型的最大版本和最大日期的索引（index_id）。

像这样：

+----------+------------+-----------+------+
| index_id | version_id |   date    | type |
+----------+------------+-----------+------+
|        1 |          4 | today     | C    |
|        1 |          4 | today     | U    |
|        2 |          4 | today     | C    |
|        2 |          3 | yesterday | U    |
|        3 |          7 | yesterday | C    |
|        3 |          8 | today     | U    |
+----------+------------+-----------+------+

我想问你一些关于这个问题的想法。谢谢。

标签： sqloracle

Answer 1

我会去row_number()：

select t.*
from (select t.*,
             row_number() over (partition by index_id, type order by version_id desc) as seqnum
      from t
     ) t
where seqnum = 1;