合并两个 MySQL 查询结果的公共部分

发布于 2025-01-05 00:25:20 字数 748 浏览 1 评论 0原文

我有 2 个查询：第一个为我提供了一份与俱乐部有公开合同的球员列表（无结束日期）

select ejl_players.id,ejl_players.surname ,ejl_club.short_name
from ejl_players
left join ejl_player_rights  r on (r.player_id =ejl_players.id and r.start_date=
(select max(start_date) from ejl_player_rights  where player_id=ejl_players.id  and end_date is null))
left join ejl_clubs on  ejl_club.id=r.club_id
where ejl_club.short_name is not null

第二个为我提供了一份自 2010 年以来未参加过比赛的球员列表

select s.player_id from ejl_play_stat s
left join ejl_protocols p on p.id=s.protocol_id
group by s.player_id
having max(pdate)<'2010-1-1')

我想获取两个结果中都存在的球员列表，即拥有开放合同但未上场 2 年的球员。第二个查询返回 10 000 条记录，当我尝试像 Jonny White 推荐的那样组合它们时，我的查询没有返回（至少在 30 分钟内没有返回）

原文

I have 2 queries: one gives me a list of players who have open contract with club (no end date)

select ejl_players.id,ejl_players.surname ,ejl_club.short_name
from ejl_players
left join ejl_player_rights  r on (r.player_id =ejl_players.id and r.start_date=
(select max(start_date) from ejl_player_rights  where player_id=ejl_players.id  and end_date is null))
left join ejl_clubs on  ejl_club.id=r.club_id
where ejl_club.short_name is not null

Second gives me a list of players who haven't played since 2010

select s.player_id from ejl_play_stat s
left join ejl_protocols p on p.id=s.protocol_id
group by s.player_id
having max(pdate)<'2010-1-1')

I want to get the list of players who exist in both results, i.e. players who have open contract , but haven't played 2 years.
Second query gives back 10 000 records and when I tried to combine them like Jonny White recommended then my query didn't return (at least not in 30 min)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只有一腔孤勇 2025-01-12 00:25:20

这应该可以做到：

select ejl_players.id,ejl_players.surname ,ejl_club.short_name
from ejl_players
left join ejl_player_rights  r on (r.player_id =ejl_players.id and r.start_date=
(select max(start_date) from ejl_player_rights  where player_id=ejl_players.id  and end_date is null))
left join ejl_clubs on  ejl_club.id=r.club_id
where ejl_club.short_name is not null
and ejl_players.id IN (select s.player_id from ejl_play_stat s
left join ejl_protocols p on p.id=s.protocol_id
group by s.player_id
having max(pdate)<'2010-1-1')

This should do it:

select ejl_players.id,ejl_players.surname ,ejl_club.short_name
from ejl_players
left join ejl_player_rights  r on (r.player_id =ejl_players.id and r.start_date=
(select max(start_date) from ejl_player_rights  where player_id=ejl_players.id  and end_date is null))
left join ejl_clubs on  ejl_club.id=r.club_id
where ejl_club.short_name is not null
and ejl_players.id IN (select s.player_id from ejl_play_stat s
left join ejl_protocols p on p.id=s.protocol_id
group by s.player_id
having max(pdate)<'2010-1-1')

回复收藏 0 原文

~没有更多了~