如果匹配查询，则返回两个值的结果的MySQL查询
如果匹配查询，则返回两个值的结果的MySQL查询
提问于 2019-04-07 15:23:52
如果或条件匹配，我想查询两个表以获得匹配的值并返回结果。以下是仅显示相关列的表格..。
scores
scoreId int not null,
comments varchar(128)
mediaComments
contentId varchar(40) not null,
scoreId int (foreign key to scores.scoreId)我想为注释和contentId获得以下条件的值.
注释!= "“或contentId存在于scoreId
这是表值..。
mysql> select * from mediaComments;
+---------+-----------------------------+
| scoreId | contentId |
+---------+-----------------------------+
| 1 | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 2 | CON-RgNzMie5F8EdSnEq7122siV |
| 4 | CON-3bo1iGIBdu623TS4ltggytT |
| 6 | CON-qjMVn2THP6d2nCta9JWL1na |
+---------+-----------------------------+
4 rows in set (0.00 sec)
mysql> select scoreId, comments from scores;
+---------+---------------------------+
| scoreId | comments |
+---------+---------------------------+
| 1 | |
| 2 | ReadScoreCommentsMethods1 |
| 3 | ReadScoreCommentsMethods2 |
| 4 | ReadScoreCommentsMethods3 |
| 5 | ReadScoreCommentsMethods4 |
| 6 | ReadScoreCommentsMethods5 |
| 7 | |
| 8 | |
| 9 | |
+---------+---------------------------+
9 rows in set (0.00 sec)下面是我使用的简单查询。
select
s.scoreId,
s.comments,
m.contentId
from
scores s,
mediaComments m
where
s.comments != '' or
m.scoreId = s.scoreId;如果注释或mediaComments都有值，我将尝试获得结果。如果这些值与查询不匹配，我希望返回NULL。例如，如果在m.contentId中找不到s.scoreId，但是s.comments有一个值，我希望将mediaComments作为NULL返回。
这是我得到的结果。
mysql> select
-> s.scoreId,
-> s.comments,
-> m.contentId
-> from
-> scores s,
-> mediaComments m
-> where
-> s.comments != '' or
-> m.scoreId = s.scoreId;
+---------+---------------------------+-----------------------------+
| scoreId | comments | contentId |
+---------+---------------------------+-----------------------------+
| 1 | | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 2 | ReadScoreCommentsMethods1 | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 2 | ReadScoreCommentsMethods1 | CON-RgNzMie5F8EdSnEq7122siV |
| 2 | ReadScoreCommentsMethods1 | CON-3bo1iGIBdu623TS4ltggytT |
| 2 | ReadScoreCommentsMethods1 | CON-qjMVn2THP6d2nCta9JWL1na |
| 3 | ReadScoreCommentsMethods2 | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 3 | ReadScoreCommentsMethods2 | CON-RgNzMie5F8EdSnEq7122siV |
| 3 | ReadScoreCommentsMethods2 | CON-3bo1iGIBdu623TS4ltggytT |
| 3 | ReadScoreCommentsMethods2 | CON-qjMVn2THP6d2nCta9JWL1na |
| 4 | ReadScoreCommentsMethods3 | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 4 | ReadScoreCommentsMethods3 | CON-RgNzMie5F8EdSnEq7122siV |
| 4 | ReadScoreCommentsMethods3 | CON-3bo1iGIBdu623TS4ltggytT |
| 4 | ReadScoreCommentsMethods3 | CON-qjMVn2THP6d2nCta9JWL1na |
| 5 | ReadScoreCommentsMethods4 | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 5 | ReadScoreCommentsMethods4 | CON-RgNzMie5F8EdSnEq7122siV |
| 5 | ReadScoreCommentsMethods4 | CON-3bo1iGIBdu623TS4ltggytT |
| 5 | ReadScoreCommentsMethods4 | CON-qjMVn2THP6d2nCta9JWL1na |
| 6 | ReadScoreCommentsMethods5 | CON-i6FYCiNDJ0u7mbGfttK8Joy |
| 6 | ReadScoreCommentsMethods5 | CON-RgNzMie5F8EdSnEq7122siV |
| 6 | ReadScoreCommentsMethods5 | CON-3bo1iGIBdu623TS4ltggytT |
| 6 | ReadScoreCommentsMethods5 | CON-qjMVn2THP6d2nCta9JWL1na |
+---------+---------------------------+-----------------------------+
21 rows in set (0.00 sec)回答 2
Stack Overflow用户
回答已采纳
发布于 2019-04-07 15:26:21
这是你想要的吗？
select s.*
from scores s
where s.comment <> '' or
exists (select 1
from mediaComments mc
where mc.scoreid = s.scoreid
);如果您也想要content_id，那么如下所示：
select s.*, mc.contentid
from scores s left join
mediaComments mc
on mc.scoreid = s.scoreid
where s.comment <> null or mc.contentid is not null;Stack Overflow用户
发布于 2019-04-07 15:38:22
不太清楚你想要什么。但是，您可能应该使用外部联接。FULL OUTER JOIN返回两个表中的所有内容，不管它是否为空。
SELECT
s.scoreId,
s.comments,
m.contentId
FROM
scores as s
FULL OUTER JOIN mediaComments as m
ON s.scoreID = m.contentId;
```mysql页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55560573
复制相关文章
相似问题
腾讯云开发者
