我有一个有关参加活动的消费者的MySQL数据库。该数据库包含一个已连接的用户表,事件表和签入表。但是数据库仅包含有关参加某个事件的用户的信息。
我想要一个遍历几个事件并检查用户是否进入其中一个事件的查询,并给出结果:
Preffered result
我尝试了以下查询:
select attend.idperson, attend.idmeeting,
(select case when
attend.idmeeting = 2901 or
attend.idmeeting = 9044 or
attend.idmeeting = 9161 or
attend.idmeeting = 2626
then 1 else 0 end) as attended
from attend
join meetings on attend.idmeeting = meetings.idmeeting
join persons on attend.idperson = persons.id
where meetings.verified = 1;
但这导致人员实际去过的所有会议,如果在参加查询的情况下选择了其中一个会议,则将会议分配为1,将其余会议分配为0。目标是使用逻辑回归预测出勤率,该解决方案使所有其他用户数据产生偏差。
最佳答案
您可以建立所有人员和会议的列表,然后加入出席者详细信息:
select distinct a1.*,
case
when attend.idperson is null then 0
else 1
end as attended
from
(
select meetings.idmeeting, persons.id as p_id
from meetings
cross join persons
where meetings.verified = 1
) a1
left join attend
on attend.idmeeting = a1.idmeetings
and attend.personid a1.p_id