一个SQL查询将在需要的地方多次返回同一成员

在Oracle 12中，您可以使用LATERAL联接:

SELECT member_code,
       error_status
FROM   your_table_name
       CROSS JOIN LATERAL (
         SELECT 'Forename less than 3 characters' AS error_status,
                1 AS priority
         FROM   DUAL
         WHERE  LENGTH(forename) < 3
         UNION ALL
         SELECT 'Date of birth after 31/01/2008' AS error_status,
                1 AS priority
         FROM   DUAL
         WHERE  date_of_birth > DATE '2008-01-31'
         UNION ALL
         SELECT 'Forename is equal to surname' AS error_status,
                1 AS priority
         FROM   DUAL
         WHERE forename = surname
         UNION ALL
         SELECT 'No Error' AS error_status,
                2 AS priority
         FROM   DUAL
         ORDER BY priority
         FETCH FIRST ROW WITH TIES
       );

在早期版本中，您可以使用多个CASE表达式，然后使用UNPIVOT表达式将列转换为行:

SELECT member_code,
       error_status
FROM   (
  SELECT member_code,
         err1,
         err2,
         err3,
         CASE 
         WHEN err1 IS NULL AND err2 IS NULL AND err3 IS NULL
         THEN 'No Error'
         END AS no_error
  FROM   (
    SELECT member_code,
           CASE WHEN LENGTH(forename) < 3 THEN 'Forename less than 3 characters' END
             AS err1,
           CASE WHEN date_of_birth > DATE '2008-01-31' THEN 'Date of birth after 31/01/2008' END
             AS err2,
           CASE WHEN forename = surname THEN 'Forename is equal to surname' END
             AS err3
    FROM   your_table_name
  )
)
UNPIVOT (error_status FOR type IN (err1, err2, err3, no_error));

其中，对于样本数据:

CREATE TABLE your_table_name (member_code, forename, surname, date_of_birth) AS
SELECT 1, 'Ng',     'Ng',     DATE '2010-01-01' FROM DUAL UNION ALL
SELECT 2, 'Thomas', 'Thomas', DATE '2008-01-01' FROM DUAL UNION ALL
SELECT 3, 'Alice',  'Abbot',  DATE '1970-01-01' FROM DUAL;

两个输出:

fiddle个