带有3个联接表的Oracle SQL Distinct

Question

我想加入我的3个表。

目前我只设置了2张表。现在很困惑如何加入。

表：WA_GA_TBL_ARTICLES，属性为：ARTICLEID和INVENTORYID_FK。

以下是查询：

SELECT
DISTINCT
S.FORM_NO,
S.ARTICLEID_FK,
S.BOX_SERIAL_NO,
S.ACTUAL_WEIGHT,
TO_CHAR(S.DATEADDED, 'DD-MON-YYYY HH24:MI:SS') AS DATEADDEDS,
S.ADDEDBY,
B.BOX_SERIAL_NO,
B.ARTICLEID_FK,
B.DATE_CODE,
B.SO_NO
FROM WA_LA_TBL_ARTICLES_SCAN S JOIN
WA_LA_TBL_ARTICLES_BOX_SN B
ON S.BOX_SERIAL_NO = B.BOX_SERIAL_NO AND
TO_CHAR(S.DATEADDED, 'YYYY-MM-DD HH24:MI') BETWEEN '2018-08-01 08:00' AND '2018-08-02 07:59'
ORDER BY S.ARTICLEID_FK, B.BOX_SERIAL_NO ASC

我的问题是，如何使用参数ARTICLEID = B.ARTICLEID_FK加入上面的WA_GA_TBL_ARTICLES查询

知道吗？

Answer 1

SELECT DISTINCT
S.FORM_NO,
S.ARTICLEID_FK,
S.BOX_SERIAL_NO,
S.ACTUAL_WEIGHT,
TO_CHAR(S.DATEADDED, 'DD-MON-YYYY HH24:MI:SS') AS DATEADDEDS,
S.ADDEDBY,
B.BOX_SERIAL_NO,
B.ARTICLEID_FK,
B.DATE_CODE,
B.SO_NO
FROM WA_LA_TBL_ARTICLES_SCAN S 
JOIN WA_LA_TBL_ARTICLES_BOX_SN B
ON S.BOX_SERIAL_NO = B.BOX_SERIAL_NO 
AND TO_CHAR(S.DATEADDED, 'YYYY-MM-DD HH24:MI') BETWEEN '2018-08-01 08:00' AND '2018-08-02 07:59'
join WA_GA_TBL_ARTICLES w  on  w.ARTICLEID = B.ARTICLEID_FK
ORDER BY S.ARTICLEID_FK, B.BOX_SERIAL_NO ASC

Answer 2

如果我理解，你可以添加加入

SELECT
DISTINCT
S.FORM_NO,
S.ARTICLEID_FK,
S.BOX_SERIAL_NO,
S.ACTUAL_WEIGHT,
TO_CHAR(S.DATEADDED, 'DD-MON-YYYY HH24:MI:SS') AS DATEADDEDS,
S.ADDEDBY,
B.BOX_SERIAL_NO,
B.ARTICLEID_FK,
B.DATE_CODE,
B.SO_NO
FROM WA_LA_TBL_ARTICLES_SCAN S 
JOIN WA_LA_TBL_ARTICLES_BOX_SN B ON S.BOX_SERIAL_NO = B.BOX_SERIAL_NO AND
TO_CHAR(S.DATEADDED, 'YYYY-MM-DD HH24:MI') BETWEEN '2018-08-01 08:00' AND '2018-08-02 07:59'
JOIN  WA_GA_TBL_ARTICLES C ON C.ARTICLEID = B.ARTICLEID_FK 
ORDER BY S.ARTICLEID_FK, B.BOX_SERIAL_NO ASC

并在选择中添加所需的WA_GA_TBL_ARTICLES C