时间戳

首页 > 解决方案 > SQL 内连接不正确的结果

mysql - SQL 内连接不正确的结果

问题描述

我的问题如下：

我正在尝试使用 WordNet MySQL，尝试添加除了已经存在的视图之外的另一个视图，例如：

CREATE OR REPLACE VIEW synsetsXsemlinksXsynsets AS 
SELECT 
       linkid,s.synsetid AS ssynsetid
       ,s.definition AS sdefinition
       ,d.synsetid AS dsynsetid
       ,d.definition AS ddefinition 
FROM synsets AS s 
     INNER JOIN 
     semlinks AS l ON s.synsetid = l.synset1id 
     INNER JOIN 
     synsets AS d ON l.synset2id = d.synsetid;

因此，以该代码作为参考，我尝试执行以下操作：

CREATE VIEW graph AS  
SELECT 
       l.linkid
       ,s.synsetid AS sssinsetid
       ,w.lemma AS swlemma
       ,s.definition AS sdefinition
       ,d.synsetid AS dsynsetid
       ,w.lemma AS dwlemma
       ,d.definition AS ddefinition 
FROM synsets AS s 
     INNER JOIN  
     semlinks AS l ON s.synsetid = l.synset1id 
     INNER JOIN  
     synsets AS d ON l.synset2id = d.synsetid 
     INNER JOIN  
     lexlinks AS x ON l.synset1id = x.synset1id 
                      AND l.synset2id = x.synset2id 
     INNER JOIN  
     words AS w ON w.wordid = x.word1id 
                      AND x.word2id = w.wordid

为了得到：linkid | ssynsetid | swlemma | sdefition| dsynsetid | dwlemma | ddefition。

但它没有按预期工作。虽然第一个给了我 285639 行，但我只给了我 12 行。我不确定问题出在哪里。有什么帮助吗？

编辑：

我期望发生的是，该语句将返回 sslemma 中的单词和 dwlemma 中的引理。所以我想我会得到使用的词和它们的引理形式。谢谢。

标签： mysqlsqlinner-joinwordnet

解决方案

对 2 个额外的表使用 LEFT JOIN。

 ...
 FROM synsets AS s 
 INNER JOIN  
 semlinks AS l ON s.synsetid = l.synset1id 
 INNER JOIN  
 synsets AS d ON l.synset2id = d.synsetid 
 LEFT JOIN  
 lexlinks AS x ON l.synset1id = x.synset1id 
                  AND l.synset2id = x.synset2id 
 LEFT JOIN  
 words AS w ON w.wordid = x.word1id 
                  AND x.word2id = w.wordid

因为如果使用 INNER JOIN 那么它会过滤那些不匹配的。

推荐阅读