SQL oracle 下一个值

Question

如何获取每个 name1 和 id 的下一个值 (nextname1)？ SQL Oracle.我尝试使用解析函数LEAD和 LAST_VALUE但不工作:

LEAD(name1) OVER (PARTITION BY id ORDER BY id) as nextname1

返回:

id name1 (nextname1)
1  AA  (AA)
2  AA  (AA)
3  AA  (MB)
4  MB  (MB)
5  MB  (BB)
6  BB  (BB)
7  BB  (ZZ)
8  ZZ  (null)

我想要:

id name1 (nextname1)
1  AA  (MB)
2  AA  (MB)
3  AA  (MB)
4  MB  (BB)
5  MB  (BB)
6  BB  (ZZ)
7  BB  (ZZ)
8  ZZ  (null)

注意:我有一个包含数百万条记录的表。

Answer 1

你可以通过两个步骤来做到这一点:

SQL> WITH DATA AS (
  2               SELECT 1 id, 'AA' name FROM DUAL
  3     UNION ALL SELECT 2 id, 'AA' name FROM DUAL
  4     UNION ALL SELECT 3 id, 'AA' name FROM DUAL
  5     UNION ALL SELECT 4 id, 'MB' name FROM DUAL
  6     UNION ALL SELECT 5 id, 'MB' name FROM DUAL
  7     UNION ALL SELECT 6 id, 'BB' name FROM DUAL
  8     UNION ALL SELECT 7 id, 'BB' name FROM DUAL
  9     UNION ALL SELECT 8 id, 'ZZ' name FROM DUAL
 10  )
 11  SELECT v.*, max(first_step) OVER (PARTITION BY name) nextname
 12    FROM (SELECT data.*,
 13                 nullif(lead(name) OVER (ORDER BY id), name) first_step
 14            FROM data) v
 15   ORDER BY id;

        ID NA FI NE
---------- -- -- --
         1 AA    MB
         2 AA    MB
         3 AA MB MB
         4 MB    BB
         5 MB BB BB
         6 BB    ZZ
         7 BB ZZ ZZ
         8 ZZ