如何校验FAILURE表中PROBLEM/FAILURE/SOLUTION列数据互不重复并标记错误？

你之前的SQL语句无效，核心原因是误用了不存在的字段——你的表只有PROBLEM、FAILURE、SOLUTION三列，并没有ProblemID、FailureID、SolutionID这类带ID的字段。

根据你的需求（校验每列数据不得包含另外两列的数据，并标记对应错误类型），可以用以下几种方式实现：

方式1：标记单个优先级错误 #

适合只需要显示最优先错误的场景（优先级：Problem→Failure→Solution）：

SELECT
  PROBLEM,
  FAILURE,
  SOLUTION,
  CASE
    WHEN PROBLEM IN (FAILURE, SOLUTION) THEN 'incorrect problem'
    WHEN FAILURE IN (PROBLEM, SOLUTION) THEN 'incorrect failure'
    WHEN SOLUTION IN (PROBLEM, FAILURE) THEN 'incorrect solution'
    ELSE 'valid'
  END AS error_status
FROM FAILURE
WHERE
  PROBLEM IN (FAILURE, SOLUTION)
  OR FAILURE IN (PROBLEM, SOLUTION)
  OR SOLUTION IN (PROBLEM, FAILURE);

方式2：标记所有存在的错误 #

如果需要同时显示一行中所有的错误类型，可以通过字符串拼接实现：

SELECT
  PROBLEM,
  FAILURE,
  SOLUTION,
  TRIM(CONCAT(
    CASE WHEN PROBLEM IN (FAILURE, SOLUTION) THEN 'incorrect problem ' ELSE '' END,
    CASE WHEN FAILURE IN (PROBLEM, SOLUTION) THEN 'incorrect failure ' ELSE '' END,
    CASE WHEN SOLUTION IN (PROBLEM, FAILURE) THEN 'incorrect solution ' ELSE '' END
  )) AS error_status
FROM FAILURE
WHERE
  PROBLEM IN (FAILURE, SOLUTION)
  OR FAILURE IN (PROBLEM, SOLUTION)
  OR SOLUTION IN (PROBLEM, FAILURE);

补充：模糊匹配场景 #

如果你的需求是“列内容包含另外两列的子串”而非精确匹配，可将IN替换为LIKE：

SELECT
  PROBLEM,
  FAILURE,
  SOLUTION,
  TRIM(CONCAT(
    CASE WHEN PROBLEM LIKE CONCAT('%', FAILURE, '%') OR PROBLEM LIKE CONCAT('%', SOLUTION, '%') THEN 'incorrect problem ' ELSE '' END,
    CASE WHEN FAILURE LIKE CONCAT('%', PROBLEM, '%') OR FAILURE LIKE CONCAT('%', SOLUTION, '%') THEN 'incorrect failure ' ELSE '' END,
    CASE WHEN SOLUTION LIKE CONCAT('%', PROBLEM, '%') OR SOLUTION LIKE CONCAT('%', FAILURE, '%') THEN 'incorrect solution ' ELSE '' END
  )) AS error_status
FROM FAILURE
WHERE
  PROBLEM LIKE CONCAT('%', FAILURE, '%') OR PROBLEM LIKE CONCAT('%', SOLUTION, '%')
  OR FAILURE LIKE CONCAT('%', PROBLEM, '%') OR FAILURE LIKE CONCAT('%', SOLUTION, '%')
  OR SOLUTION LIKE CONCAT('%', PROBLEM, '%') OR SOLUTION LIKE CONCAT('%', FAILURE, '%');

内容的提问来源于stack exchange，提问作者stondd123