sql-查询有问题,需要根据某些条件进行选择

snz8szmq  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(289)

我有这两个表,在第一个表(writes)中是存储在db中的书籍(doi标识书籍代码和orcid书籍作者,在第二个表(quotes)中是每本书的引号)。我试图达到的结果是,拥有作者的识别码,这些作者在他们的书中提到了其他作者而不是他们自己写的书
写入

Doi   Orcid
1     100
2     200
3     300
4     100

引用

Doi DoiMentioned
1          4
2          3
3          4

预期产量:

ORCID Doi    DoiMentioed
200    2         3
300    3         4

我的代码:

(select w.orcid
from writes w
join quotes q
on q.doi = w.doi

minus

select w.orcid
from writes w
join quotes q
on q.doimentioned = w.doi);

我也尝试了内部连接,但结果不是我所希望的

tzdcorbm

tzdcorbm1#

内部连接应该可以工作,但是您需要使用 WRITES 表格两次:一次为提及的作者,一次为提及的作者。然后比较它们以确定它们是不同的。

SELECT w.orcid, w.doi, q.doimentioned
FROM    writes w
INNER JOIN quotes q on q.doi = w.doi
INNER JOIN writes w2 ON w2.doi = q.doiMentioned
WHERE w2.orcid != w.orcid;

完整示例,以及op提供的数据,以显示其工作原理:

WITH writes (doi, orcid) AS 
  ( SELECT 1, 100 FROM DUAL UNION ALL
    SELECT 2, 200 FROM DUAL UNION ALL
    SELECT 3, 300 FROM DUAL UNION ALL
    SELECT 4, 100 FROM DUAL ),
  quotes ( Doi, DoiMentioned ) AS 
  ( SELECT 1,4 FROM DUAL UNION ALL
    SELECT 2,3 FROM DUAL UNION ALL
    SELECT 3,4 FROM DUAL )
SELECT w.orcid, w.doi, q.doimentioned
FROM    writes w
INNER JOIN quotes q on q.doi = w.doi
INNER JOIN writes w2 ON w2.doi = q.doiMentioned
WHERE w2.orcid != w.orcid;
+-------+-----+--------------+
| ORCID | DOI | DOIMENTIONED |
+-------+-----+--------------+
|   200 |   2 |            3 |
|   300 |   3 |            4 |
+-------+-----+--------------+

相关问题