mysql - 独特的电影通过UNION两个连接的结果，MYSQL

Question

我运行以下查询来 UNION 以下 4 个表的两个联接的结果:

SELECT member_CROWD.worker_Id, member_CROWD.Unreliable, Answer_CROWD.qId, Answer_CROWD.answer
FROM member_CROWD
JOIN `Answer_CROWD` 
ON member_CROWD.worker_Id = Answer_CROWD.worker_Id 
WHERE member_CROWD.Unreliable = '0' AND Answer_CROWD.qId = 'q8'
GROUP BY Answer_CROWD.answer

UNION

SELECT member.mem_id, member.Unreliable, Answer.qId, Answer.answer
FROM member
JOIN `Answer` 
ON member.mem_id = Answer.userId 
WHERE member.Unreliable = '0' AND Answer.qId = 'q8'
GROUP BY Answer.answer

这是因为我有两个实验来收集数据；一种是通过让我的 friend 填写我的调查(数据存储在member和Answer表中)，另一种是通过众包(数据存储在member_CROWD和Answer_CROWD中)。

现在的问题是，我想知道例如“用户选择的电影的唯一数量”，但来自所有用户( friend 和人群)。上面的查询可以联合两个连接结果，但答案仍然不唯一(因为它们仅在每个连接结果中是唯一的)。

有人可以帮助我知道如何在整个实验中获得独特的#of 电影吗？

谢谢

Answer 1

Distinct 可以在 mysql 的计数中运行，这非常方便。

select Count(Distinct answer) from (
SELECT member_CROWD.worker_Id, member_CROWD.Unreliable, Answer_CROWD.qId, Answer_CROWD.answer
FROM member_CROWD
JOIN `Answer_CROWD` 
ON member_CROWD.worker_Id = Answer_CROWD.worker_Id 
WHERE member_CROWD.Unreliable = '0' AND Answer_CROWD.qId = 'q8'
GROUP BY Answer_CROWD.answer

UNION

SELECT member.mem_id, member.Unreliable, Answer.qId, Answer.answer
FROM member
JOIN `Answer` 
ON member.mem_id = Answer.userId 
WHERE member.Unreliable = '0' AND Answer.qId = 'q8'
GROUP BY Answer.answer
) a