这是我的查询
SELECT tum.user_id, tum.first_name, tum.last_name
FROM di_webinar t
LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
INNER JOIN tbl_user_master tum ON tum.user_id = ter.user_ref_id OR tum.user_id = tea.user_ref_id
WHERE t.di_ref_id ='93'
GROUP BY tum.user_id
该查询工作正常，可以得到预期的结果，但是由于内部联接的OR条件，它的运行速度非常慢。这是我试图使其更好的方法。
SELECT tum.user_id, tum.first_name, tum.last_name
FROM di_webinar t
LEFT JOIN (
SELECT event_ref_id, user_ref_id
FROM tbl_event_registrants GROUP BY user_ref_id
) ter ON ter.event_ref_id = t.webinar_ref_id
LEFT JOIN (
SELECT event_ref_id, user_ref_id
FROM tbl_event_attendees GROUP BY user_ref_id
) tea ON tea.event_ref_id = t.webinar_ref_id
-- LEFT JOIN tbl_event_registrants ter ON ter.event_ref_id = t.webinar_ref_id
-- LEFT JOIN tbl_event_attendees tea ON tea.event_ref_id = t.webinar_ref_id
INNER JOIN tbl_user_master tum ON tum.user_id = ter.user_ref_id OR tum.user_id = tea.user_ref_id
WHERE t.di_ref_id ='93'
GROUP BY tum.user_id
但是我不确定那是最好的方法。
这是解释计划
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- --------------------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------- ------- -------------------------------- ------ ----------------------------------------------------
1 PRIMARY t ref FK_di_webinar_direfid FK_di_webinar_direfid 2 const 1 Using temporary; Using filesort
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 4 univarie_uni_db.t.webinar_ref_id 36 Using index
1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 4 univarie_uni_db.t.webinar_ref_id 11 Using index
1 PRIMARY tum ALL PRIMARY,UNQ_tbl_user_master_LoginRefId,idx_user_master_membership_ref_id,FK_user_master_board_ref_id,FK_tbl_user_master_school_id,FK_tbl_user_master_profile_statusID,FK_tbl_user_master_payment_statusID,FK_tbl_user_master_url_ref_id,idx_tbl_user_master_FirstName,idx_tbl_user_master_LastName,fk_tbl_user_master_CityRefId,fk_tbl_user_master_StateRefId,fk_tbl_user_master_CountryRefId,fk_tbl_user_master_EthnicityRefId,fk_tbl_user_master_FamilyIncomeRefId,fk_tbl_user_master_ScholarshipImportanceRefId,fk_tbl_user_master_ReasonStudyAbroadRefId,fk_tbl_user_master_PrimaryFinancingRefId,fk_tbl_user_master_CitizenRefId,Idx_tbl_user_master_CriticalPercent,Idx_tbl_user_master_PresentClass,Idx_tbl_user_master_ClassStatus,srinivas_test (NULL) (NULL) (NULL) 35641 Using where; Using join buffer (Block Nested Loop)
3 DERIVED tbl_event_attendees index fk_tbl_event_attendees_UserRefId fk_tbl_event_attendees_UserRefId 5 (NULL) 845 (NULL)
2 DERIVED tbl_event_registrants index fk_tbl_event_registrants_UserRefId fk_tbl_event_registrants_UserRefId 5 (NULL) 3568 (NULL)
在您说的评论中，您不必为每个用户显示示例网络研讨会。然后，您甚至不必从网络研讨会表中进行选择。毕竟，您只需要参与网络研讨会的所有用户。因此，请从tbl_user_master中进行选择，并确保在两个表tbl_event_registrants和tbl_event_attendees之一中提到了用户ID。
select first_name, last_name
from tbl_user_master
where user_id in
(
select user_ref_id
from tbl_event_registrants
union all
select user_ref_id
from tbl_event_attendees
);
但是，MySQL在IN子句和UNION查询上可能会非常慢。因此，这与两个EXISTS子句相同：
select first_name, last_name
from tbl_user_master tum
where exists
(
select *
from tbl_event_registrants ter
where ter.user_ref_id = tum.user_id
)
or exists
(
select *
from tbl_event_attendees tea
where tea.user_ref_id = tum.user_id
);
如果仅想显示参加所有网络研讨会的用户，则必须找到网络研讨会的总数，并与与用户关联的网络研讨会的数量进行比较。
select first_name, last_name
from tbl_user_master tum
where
(
select count(distinct event_ref_id)
from
(
select event_ref_id
from tbl_event_registrants
where user_ref_id = tum.user_id
union all
select event_ref_id
from tbl_event_attendees
where user_ref_id = tum.user_id
)
) = (select count(*) from di_webinar);
编辑：这与联接相同：
select tum.first_name, tum.last_name
from tbl_user_master tum
join
(
select user_ref_id, event_ref_id
from tbl_event_registrants
union
select user_ref_id, event_ref_id
from tbl_event_attendees
) ref on ref.user_ref_id = tum.user_id
group by tum.user_id
having count(*) = (select count(*) from di_webinar);
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句