我需要从排序所选行返回列号

Question

我有一个这样的表:

| uid |    date    |
+-----+------------+ 
| 032 | 16-04-2022 |
| 453 | 15-04-2022 |
| 425 | 13-04-2022 |
| 563 | 14-04-2022 |

我需要排序它们，并返回新的列，像这样:

| uid |    date    | num |
+-----+------------+-----+
| 425 | 13-04-2022 |  1  |
| 563 | 14-04-2022 |  2  |
| 453 | 15-04-2022 |  3  |
| 032 | 16-04-2022 |  4  |

Answer 1

WITH CTE(UID,DATED)AS
(
  SELECT '032',TO_DATE('16-04-2022','DD-MM-YYYY')UNION ALL
  SELECT '453',TO_DATE('15-04-2022','DD-MM-YYYY')UNION ALL 
  SELECT '425',TO_DATE('13-04-2022','DD-MM-YYYY')UNION ALL
  SELECT '563',TO_DATE('14-04-2022','DD-MM-YYYY') 
)
SELECT C.UID,C.DATED,
  ROW_NUMBER()OVER(ORDER BY C.DATED ASC)NUM
 FROM CTE AS C

可以使用ROW_NUMBER()-function。CTE是表数据的表示