我有一张选票,每个选民获得 3 票,从 10 名不同的候选人中进行选择。投票 1 得 3 分,投票 2 得 2 分,投票 3 得 1 分。
我有以下 SQL 查询来计算每次投票获得的总分(因此投票 1、2 和 3 的结果是分开的)。
我需要做的是将所有这些结果放在一个表中,但我不太确定从哪里开始。
SELECT cn.cand_name, (count(vote_1) * 3) as vote_1 FROM candidate_votes cv Inner Join candidate_names cn ON cv.vote_1 = cn.cand_number GROUP BY cand_name;
SELECT cn.cand_name, (count(vote_2) * 2) as vote_2 FROM candidate_votes cv Inner Join candidate_names cn ON cv.vote_2 = cn.cand_number GROUP BY cand_name;
SELECT cn.cand_name, (count(vote_3) * 1) as vote_3 FROM candidate_votes cv Inner Join candidate_names cn ON cv.vote_3 = cn.cand_number GROUP BY cand_name;
我有以下结果表:
Voter_number Vote_1 Vote2 Vote3
123 cand_1 cand_3 cand_2
456 cand_2 cand_1 cand_3
789 cand_2 cand_3 cand_1
以及以下候选姓名表:
cand_number cand_name
cand_1 Dave
cand_2 Sarah
cand_3 Nigel
所以我正在寻找的结果将类似于:
Candidate Votes
Dave 6
Sarah 7
Nigel 5
SELECT
cn.cand_name,
count(cv1.vote_1) * 3 as vote_1,
count(cv2.vote_2) * 2 as vote_2,
count(cv3.vote_3) as vote_3
FROM
candidate_names cn
LEFT JOIN
candidate_votes cv1 ON cv1.vote_1 = cn.cand_number
LEFT JOIN
candidate_votes cv2 ON cv2.vote_2 = cn.cand_number
LEFT JOIN
candidate_votes cv3 ON cv3.vote_3 = cn.cand_number
GROUP BY cn.cand_name;
这也允许您添加所有投票
(count(cv1.vote_1) * 3) +
(count(cv2.vote_2) * 2) +
count(cv3.vote_3) as totalvotes
编辑:行被 JOIN 相乘,这就是 cand2 和 cand3 错误的原因
SELECT
cn.cand_name,
SUM(CASE WHEN cv.vote_1 = cn.cand_number THEN 3 ELSE 0 END) as vote_1,
SUM(CASE WHEN cv.vote_2 = cn.cand_number THEN 2 ELSE 0 END) as vote_2,
SUM(CASE WHEN cv.vote_3 = cn.cand_number THEN 1 ELSE 0 END) as vote_3
FROM
candidate_names cn
JOIN
candidate_votes cv ON cn.cand_number IN (cv.vote_1, cv.vote_2, cv.vote_3)
GROUP BY cn.cand_name;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)