为什么此查询会导致"missing FROM clause"？

Question

为什么下面的sql语句由于表错误而不断丢失FROM子句项
如何调整？

WITH SUBID AS (
SELECT * FROM "B_COLLECTION"."COLL_C_RECORD"
),
TR AS (
SELECT * FROM "B_TRACE"."PERSONAL_TC_RECORD"
)
SELECT "SUBJECT_C_ID" 
FROM "B_COLLECTION"."COLL_C_RECORD"
WHERE ( SUBID.SUBJECT_ID = TR.PERSONAL_S_ID ) 
AND ( TR.STATE_ID ='5' OR TR.STATE_ID  = 'A' OR TR.STATE_ID = 'C');

Answer 1

您声明SUBID和TR很好，但由于它们是表，所以您的select语句还不知道它们。

在这里，您需要在查询的FROM子句中输入SUBID和TR。它看起来应该有点像

WITH SUBID AS (
SELECT * FROM "B_COLLECTION"."COLL_C_RECORD"
) ,
TR AS (
SELECT * FROM "B_TRACE"."PERSONAL_TC_RECORD"
)
SELECT "SUBJECT_C_ID" FROM SUBID
WHERE SUBID."SUBJECT_ID" IN 
(SELECT "PERSONAL_S_ID" FROM TR
WHERE TR."STATE_ID" = '5'
OR TR."STATE_ID" = 'A'
OR TR."STATE_ID" = 'C');

这就是为什么只在重复查询中使用WITH子句的原因。在这里，使用实际上会更麻烦

SELECT "SUBJECT_ID" FROM "B_COLLECTION"."COLL_C_RECORD" SUBID
WHERE SUBID."SUBJECT_ID" IN 
(SELECT "PERSONAL_S_ID" FROM "B_TRACE"."PERSONAL_TC_RECORD" TR
WHERE TR."STATE_ID" = '5'
OR TR."STATE_ID" = 'A'
OR TR."STATE_ID" = 'C');