内部连接三个表似乎是错误的

Question

我想用条件内部连接三个表
一个条件是DAT.STATE_ID IN('7'，'8'，'9'(
以下查询的计数为116。

select count(*) from "B_TRACE"."P_TC_DATA" DAT WHERE DAT."STATE_ID" IN ('7','8','9');

然而，当使用下面的查询连接三个表时，计数增加到146
计数应小于116。这似乎不对
内部联接查询有什么问题？

SELECT DAT.*, SUBID.*, TR.* 
FROM "B_TRACE"."P_TC_DATA" AS DAT, 
"B_CC"."CC_CONSENT_RECORD" AS SUBID, 
"B_TRACE"."P_TC_RECORD" AS TR
WHERE TR."P_SERIAL_ID" = SUBID."SUBJECT_ID" 
AND TR."P_SERIAL_ID" = DAT."P_SERIAL_ID" 
AND  (DAT."STATE_ID" IN ('7','8','9')) 
AND (SUBID."SUBJECT_C_ID" IS NOT NULL)

再次尝试，仍有146行

SELECT DATS.*, SUBID.*, TR.* 
FROM (select * from "B_TRACE"."P_TC_DATA" DAT WHERE DAT."STATE_ID" IN ('7','8','9')) AS DATS, 
"B_CC"."CC_CONSENT_RECORD" AS SUBID, 
"B_TRACE"."P_TC_RECORD" AS TR
WHERE TR."P_SERIAL_ID" = SUBID."SUBJECT_ID" 
AND TR."P_SERIAL_ID" = DATS."P_SERIAL_ID"  
AND (SUBID."SUBJECT_C_ID" IS NOT NULL)

有些人尝试

--got 379 rows
SELECT  SUBID.*, TR.* 
FROM  
"B_CC"."CC_CONSENT_RECORD" AS SUBID, 
"B_TRACE"."P_TC_RECORD" AS TR
WHERE TR."P_SERIAL_ID" = SUBID."SUBJECT_ID" 

--got 91 rows
SELECT DATS.*, SUBID.* 
FROM (SELECT * FROM "B_TRACE"."P_TC_DATA" DAT WHERE DAT."STATE_ID" IN ('7','8','9')) AS DATS, 
"B_CC"."CC_CONSENT_RECORD" AS SUBID,    
WHERE SUBID."SUBJECT_ID"=DATS."P_SERIAL_ID" 

--got 181 rows  
SELECT DATS.*, TR.* 
FROM (SELECT * FROM "B_TRACE"."P_TC_DATA" DAT WHERE DAT."STATE_ID" IN ('7','8','9')) AS DATS,  
"B_TRACE"."P_TC_RECORD" AS TR
WHERE TR."P_SERIAL_ID" = DATS."P_SERIAL_ID"

Answer 1

CC_CONSENT_RECORD中可能存在重复的SUBJECT_ID。因此，对于每个重复，它在连接到P_TC_DATA时都会添加到计数中。

你可以测试这个

SELECT CONS.SUBJECT_ID, COUNT(*)
FROM B_TRACE.P_TC_DATA AS DATA
JOIN B_CC.CC_CONSENT_RECORD AS CONS 
ON CONS.SUBJECT_ID = DATA.P_SERIAL_ID
WHERE DATA.STATE_ID IN ('7', '8', '9')
GROUP BY CONS.SUBJECT_ID
HAVING COUNT(*) > 1

这里有一个查询的替代方案

SELECT DATA.*, CONS.*, REC.* 
FROM B_TRACE.P_TC_DATA AS DATA
JOIN B_CC.CC_CONSENT_RECORD AS CONS 
ON (CONS.SUBJECT_ID = DATA.P_SERIAL_ID AND CONS.SUBJECT_C_ID IS NOT NULL)
LEFT JOIN B_TRACE.P_TC_RECORD AS REC 
ON REC.P_SERIAL_ID = DATA.P_SERIAL_ID
WHERE DATA.STATE_ID IN ('7', '8', '9')

Answer 2

尝试如下显式加入

SELECT DAT.*, SUBID.*, TR.* 
FROM "B_TRACE"."P_TC_RECORD" AS TR join
"B_CC"."CC_CONSENT_RECORD" AS SUBID on TR."P_SERIAL_ID" = SUBID."SUBJECT_ID"
join "B_TRACE"."P_TC_DATA" AS DAT on TR."P_SERIAL_ID" = DAT."P_SERIAL_ID"
where
AND  DAT."STATE_ID" IN ('7','8','9')
AND SUBID."SUBJECT_C_ID" IS NOT NULL