postgreSQL 在 CASE 语句为 true 时返回另一个表的值

Question

我想编写一个 SQL 语句，该语句基于 CASE 语句返回一个值，该语句具有三种可能的结果。

1） 如果列值以"C0"开头，则返回表中两列的串联

2）如果列以"L0"开头，则在该行的另一列（_PARENT_PROJECT_NUMBER）中获取值，并查看该值是否位于另一个表中。 如果不是，则返回值 _PARENT_PROJECT_NUMBER。

3）如果_PARENT_PROJECT_NUMBER确实驻留在另一个表中，则返回另一个表"Rollup_Project_Mapping"列"Rollup_Project"中的值。

我在下面写的语句工作正常，除了第 12 行，"Rollup_Project_Mapping"."Rollup_Project"如果在另一个表中找到_PARENT_PROJECT_NUMBER，我希望它返回另一个表的值。 以下是错误消息：

ERROR:  syntax error at or near "FROM"
LINE 12:      FROM "Rollup_Project_Mapping"."Rollup_Project"

有谁知道当 CASE 语句的这一部分为真时如何返回另一个表中的值？

SELECT
CASE 
  WHEN LEFT("_PROJECT_NUMBER",2)='C0' THEN
    '('||"_PROJECT_NUMBER"||') '||"_PROJECT_DESCRIPTION"
  WHEN LEFT("_PROJECT_NUMBER",2)='L0' THEN
    CASE
      WHEN (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project") IS NOT NULL THEN
        "Rollup_Project_Mapping"."Rollup_Project"
    ELSE "_PARENT_PROJECT_NUMBER"
    END
END AS Rollup_Project2
FROM "PA_ACCOUNTED_TEST";

Answer 1

你可以只使用一组 WHEN/THEN 语句和EXISTS：

SELECT (CASE WHEN LEFT("_PROJECT_NUMBER",2) = 'C0'
             THEN '('||"_PROJECT_NUMBER"||') '||"_PROJECT_DESCRIPTION"
             WHEN LEFT("_PROJECT_NUMBER",2) = 'L0' AND
                  EXISTS (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project")
                  THEN (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project")
             ELSE "_PARENT_PROJECT_NUMBER"
       END) AS Rollup_Project2
FROM "PA_ACCOUNTED_TEST";

Answer 2

我根据Linoff的答案调整了原始SQL语句@Gordon以防有人希望将其作为另一种选择：

SELECT
CASE 
  WHEN LEFT("_PROJECT_NUMBER",2)='C0' THEN
    '('||"_PROJECT_NUMBER"||') '||"_PROJECT_DESCRIPTION"
  WHEN LEFT("_PROJECT_NUMBER",2)='L0' AND (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project") IS NOT NULL THEN
    (SELECT "Rollup_Project" FROM "Project_Rollup_Mapping" WHERE "PA_ACCOUNTED_TEST"."_PARENT_PROJECT_NUMBER" = "Project_Rollup_Mapping"."Project")
  ELSE "_PARENT_PROJECT_NUMBER"
END AS Rollup_Project2
FROM "PA_ACCOUNTED_TEST";