选择这两行并使用regex丢弃错误的行

SELECT DISTINCT A FROM PAYROLL_RECORDS
WHERE
   (B IN (SELECT (B - 1) FROM PAYROLL_RECORDS WHERE A = 'Employee Name') OR
    B IN (SELECT (B - 2) FROM PAYROLL_RECORDS WHERE A = 'Employee Name')) AND
   NOT regexp_like(A, '^(\d+|null)$') 
ORDER BY A;

成功也取决于“其他东西”中的内容。您可能还必须丢弃正则表达式中的其他值。

我知道您希望列中每次出现字符串'Employee Name'时的“前一个”非空值和非数字值。
我推荐使用带有条件表达式和ignore nulls选项的lag：

select distinct employee_name
from (
    select p.*,
        lag(case when not regexp_like(a, '^\d+$') then a end)
            ignore nulls 
            over(order by b) employee_name
    from payroll_records p
) p
where a = 'Employee Name'

技巧是让lag()中的表达式将所有数字值转换为null s，以便ignore null s选项在查找实际雇员姓名时跳过它们。
请注意：

• 这将执行单表扫描
• 无论需要跳过多少行，此操作都有效
  Demo-在nbk的小提琴（谢谢nbk）。

您可以使用窗口函数LEAD检查两行的值

WITH CTE AS
(SELECT
  "A",
  LEAD("A") OVER(ORDER BY "B") row1
  ,
  LEAD("A",2) OVER(ORDER BY "B") row2
  FROM 
PAYROLL_RECORDS)
SELECT "A" FROM CTE 
  WHERE (row1 = 'Employee Name' OR row2 = 'Employee Name') 
  AND (NOT REGEXP_LIKE("A", '^[[:digit:]]+$'))

fiddle
如果你有空格，你可以TRIM A

WITH CTE AS
(SELECT
  "A",
  LEAD(TRIM("A")) OVER(ORDER BY "B") row1
  ,
  LEAD(TRIM("A"),2) OVER(ORDER BY "B") row2
  FROM 
PAYROLL_RECORDS)
SELECT "A" FROM CTE 
  WHERE (row1 LIKE  'Employee Name' OR row2 LIKE 'Employee Name') 
  AND (NOT REGEXP_LIKE("A", '^[[:digit:]]+$'))

fiddle

不需要联接或子查询。在where子句的帮助下，您可以从A列中删除null和数字值，然后lag()over()窗口函数将帮助您获得每个“员工姓名”的前一个值。第一行始终为null
查询：

select distinct case when A='Employee Name' then lag(regexp_replace(A, '[0-9]', ''))over(order by B) end as A from PAYROLL_RECORDS 
  where A is not null and VALIDATE_CONVERSION(A AS NUMBER) <> 1

输出：
| 一个|
| --------------|
| 联系我们|
| 约翰·史密斯|
| 简·史密斯|
| 小雅各布·约翰·史密斯|
fiddle
如果你想删除第一行空值，那么：
查询：

select A from
(select distinct case when A='Employee Name' then lag(regexp_replace(A, '[0-9]', ''))over(order by B) end as A from PAYROLL_RECORDS 
  where A is not null and VALIDATE_CONVERSION(A AS NUMBER) <> 1 )
 t where A is not null

输出：
| 一个|
| --------------|
| 约翰·史密斯|
| 简·史密斯|
| 小雅各布·约翰·史密斯|
fiddle
在Oracle 10 g中，validate_conversion()不可用。您可以使用REGEXP_LIKE()
查询：

select A from
(select distinct case when A='Employee Name' then lag(regexp_replace(A, '[0-9]', ''))over(order by B) end as A from PAYROLL_RECORDS 
  where A is not null and not REGEXP_LIKE(A, '^[[:digit:]]+$'))
 t where A is not null

输出：
| 一个|
| --------------|
| 约翰·史密斯|
| 简·史密斯|
| 小雅各布·约翰·史密斯|
fiddle

使用第一个CTE从值中删除任何空值和数字，然后将“员工姓名”标记为1，将其他值标记为0。
第二个CTE用于检索每个“员工姓名”行的前面的行。

WITH CTE AS (
   SELECT A, B, CASE WHEN A = 'Employee Name' then 1 else 0 end as flaged
   FROM PAYROLL_RECORDS
   WHERE A is not null AND (NOT REGEXP_LIKE("A", '^[[:digit:]]+$'))
),
CTE2 AS (
  SELECT A, B, flaged, lead(flaged) over (order by B) as NameRank
  FROM CTE
)
select A
from CTE2
where NameRank = 1

Demo here