如何在MySQL中实现INTERSECT

Question

这是我当前的查询。我今天发现了INTERSECT，但是MySQL不支持它。有人能给我一些关于如何改进这个查询的建议吗？它给了我想要的结果，但相当缓慢。

如果有帮助的话，我会与运动队合作，并过滤统计数据。我先筛选主队，然后筛选客队。我使用了count(id(方法，因为我只想返回在和两个查询中找到的fixture id，所以我认为INTERSECT是完美的。

select id, count(id) as count from (

select f.id, f.etc, etc
from fixtures f
inner join team_stats t on f.id = t.fixture_id and f.league_id = t.league_id and f.home_id = t.team_id
where f.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60' and
scored_1h_home_avg between '0.7' and '6' and 
o05_1h_goals_overall_per between '76' and '100' and 
first_goal_total_avg between '0' and '35' and 
scored_o05_1h_home_per >= 69

UNION ALL

select f.id, f.etc, etc
from fixtures f
inner join team_stats t on f.id = t.fixture_id and f.league_id = t.league_id and f.away_id = t.team_id
where f.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60' and
conceded_1h_away_avg between '0.7' and '6' and 
first_goal_total_avg between '0' and '35' and 
o05_1h_goals_away_per between '77' and '100'
)    
x group by id HAVING count(id) > 1

Answer 1

您可以使用exists来获得您想要的：

select f.id
from fixtures f
inner join team_stats t on f.id = t.fixture_id and f.league_id = t.league_id and f.home_id = t.team_id
where f.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60' and scored_1h_home_avg between '0.7' and '6' and o05_1h_goals_overall_per between '76' and '100' and first_goal_total_avg between '0' and '35' and scored_o05_1h_home_per >= 69
and exists(
select 1
from fixtures f2
inner join team_stats t on f2.id = t.fixture_id and f2.league_id = t.league_id and f2.away_id = t.team_id
where f2.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60' and
conceded_1h_away_avg between '0.7' and '6' and first_goal_total_avg between '0' and '35' and o05_1h_goals_away_per between '77' and '100'
and f1.id=f2.id)

另一种变体可以是在没有union all的情况下组合两个查询。如果您的查询产生了正确的答案，那么也应该这样做。

select id, count(id) as i_count 
from fixtures f
inner join team_stats t on f.id = t.fixture_id and f.league_id = t.league_id 
where f.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60'    
and ((f.home_id = t.team_id and and scored_1h_home_avg between '0.7' and '6'  o05_1h_goals_overall_per between '76' and '100' and first_goal_total_avg between '0' and '35' and scored_o05_1h_home_per >= 69)
or (f.away_id = t.team_id and conceded_1h_away_avg between '0.7' and '6' and first_goal_total_avg between '0' and '35' and o05_1h_goals_away_per between '77' and '100'))
group by id HAVING count(id) > 1

Answer 2

如果我正确地遵循了以下内容，那么在两个查询中都是相同的：

select f.id, f.etc, etc
from fixtures f join
team_stats t
on f.id = t.fixture_id and f.league_id = t.league_id and f.away_id = t.team_id
where f.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60' and
conceded_1h_away_avg between '0.7' and '6' and 
first_goal_total_avg between '0' and '35'

然后你想要这两个条件：

scored_o05_1h_home_per >= 69
o05_1h_goals_away_per between '77' and '100'

如果是这样，聚合可能是最简单的解决方案：

select f.id, f.etc, etc
from fixtures f join
team_stats t
on f.id = t.fixture_id and f.league_id = t.league_id and f.away_id = t.team_id
where f.status in ('FT', 'AET', 'PEN', 'FT_PEN') and
unix < 1619927451 and
played_overall between '11' and '60' and
conceded_1h_away_avg between '0.7' and '6' and 
first_goal_total_avg between '0' and '35'
group by f.id
having sum(scored_o05_1h_home_per >= 69) > 0 and
sum(o05_1h_goals_away_per between '77' and '100')

我还注意到，你在看起来像数字的地方使用了单引号。如果这些真的是数字，那么去掉单引号。不要混用类型。如果值存储为字符串，则比较是字符串，而不是数字，并且可能无法达到预期效果。