如果任一匹配查询，则返回 2 个值的结果的 MySQL 查询

debugcn 发表于 Dev

杰梅尔文

我想查询 2 个表以获取匹配值并在任一条件匹配时返回结果。以下是仅显示相关列的表格...

scores
    scoreId int not null,
    comments varchar(128)

mediaComments
    contentId varchar(40) not null,
    scoreId int (foreign key to scores.scoreId)

我想根据以下条件获取评论和 contentId 的值...

评论 != "" OR 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。例如，如果在 mediaComments 中找不到 s.scoreId，我希望将 m.contentId 返回为 NULL，但 s.comments 有一个值。

这是我得到的结果......

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)

戈登·利诺夫

这是你想要的吗？

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。