mysql - 我希望在我的 in () 语句中包含重复项

Question

我想在我的查询中包含重复项。我没有成功地将我的“in”语句更改为“joins”。

我的预期结果是行数为 115。

我的结果是行数为 108。

如果我在第一个子查询中执行“分组依据”，我得到的行数为 108。

select match_id item_0, item_1,  item_2, item_3, item_4, item_5, purchase_log 
    from player_matches where match_id IN
       (select x.match_id from (select matches.match_id, picks_bans.team from matches, picks_bans where picks_bans.hero_id = 1 and picks_bans.match_id = matches.match_id and is_pick = true and start_time > 1483228800 ORDER BY start_time DESC) as x 
        inner join
             (select matches.match_id, picks_bans.team from matches, picks_bans where picks_bans.hero_id 
/*this is the statement that needs to be tweaked/changed */
               IN (2,12,47,4,99) and picks_bans.match_id = matches.match_id and is_pick = true and start_time > 1483228800 ORDER BY start_time DESC) as y  on y.match_id=x.match_id and x.team!=y.team) 
    and hero_id = 1

您可以使用 opendota inbrowser data-explorer 来更好地理解我的问题。

My subquery (returns 115)

My final Query (returns 108)

如何让我的最终查询返回 115 行计数？

我的查询也很慢，这是因为我使用了“in()”吗？

Answer 1

这是因为 IN 只会检查值是否存在。使用 INNER JOIN 代替:

select a.match_id, a.item_0, a.item_1,  a.item_2, a.item_3, a.item_4, a.item_5, a.purchase_log 
from player_matches a
    INNER JOIN
    (
        select x.match_id from (select matches.match_id, picks_bans.team from matches, picks_bans where picks_bans.hero_id = 1 and picks_bans.match_id = matches.match_id and is_pick = true and start_time > 1483228800 ORDER BY start_time DESC) as x  inner join (select matches.match_id, picks_bans.team from matches, picks_bans where picks_bans.hero_id in (2,12,47,4,5) and picks_bans.match_id = matches.match_id and is_pick = true and start_time > 1483228800 ORDER BY start_time DESC) as y  on y.match_id=x.match_id and x.team!=y.team
    ) b ON  a.match_id = b.match_id
WHERE hero_id = 1

Here's a demo from your original link.