mysql - 在淘汰候选人的同时重新分配选票

Question

考虑一下这个想法。我有一个排名选择投票系统，不符合阈值的候选人在不同的轮次中被淘汰。在此示例中，我们有 6 名候选人在初选中竞选两个席位。此时我们已经进入第 4 轮(没有获胜者)，候选人 3 和 5 已经被淘汰。在这一轮中，候选人 4 被淘汰，他/她的选票将重新分配给那些在每次投票中被标记为第二或第三选择的候选人，而候选人 4 被标记为第一选择。

我们抓取候选人 4 是第一选择的所有选票(恰好是 4 票)。它们看起来像这样:

候选人4:
4 1 6 3 2 5
4 3 1 6 2 5
4 5 6 2 3 1
4 3 1 5 6 2

重新分配的选票是(粗体):
4 1 6 3 2 5
4 3 1 6 2 5
4 5 6 2 3 1
4 3 1 5 6 2

现在解决我的问题:我需要编写一个 MySQL 查询来获取这些值并计算每个给定候选人通过此重新分配获得的票数。 IE。 MySQL 查询的结果应如下所示:

候选人1:3票
候选人6:1票

编辑:这个查询需要知道候选人 3 和 5 已经被淘汰，因此前往下一列寻找任何其他候选人。

我将如何编写 MySQL 查询来获得此结果？

Answer 1

一直在玩。

我认为如果可以使用您当前的数据布局:-

SELECT COALESCE(IF(vote_candidate_b IN (4,3,5), NULL, vote_candidate_b),
                IF(vote_candidate_c IN (4,3,5), NULL, vote_candidate_c),
                IF(vote_candidate_d IN (4,3,5), NULL, vote_candidate_d),
                IF(vote_candidate_e IN (4,3,5), NULL, vote_candidate_e),
                IF(vote_candidate_f IN (4,3,5), NULL, vote_candidate_f)) AS vote_candidate,
                COUNT(*)
FROM votes
WHERE vote_candidate_a = 4
GROUP BY vote_candidate;

这与 fancypants 的回答差不多。我不太喜欢这个，因为它看起来不容易阅读，而且(如果数据量很大)可能效率低下。

我更倾向于将数据拆分到不同的表结构中，每组选票有多行，每个候选人一个。如果系统处于开发的早期阶段(即，您只需生成新表)，这将容易得多。

假设您现有的数据设置如下:-

CREATE TABLE votes
(
    vote_id INT NOT NULL AUTO_INCREMENT,
    vote_candidate_a INT,
    vote_candidate_b INT,
    vote_candidate_c INT,
    vote_candidate_d INT,
    vote_candidate_e INT,
    vote_candidate_f INT
);

INSERT INTO votes
VALUES
(NULL, 4, 1, 6, 3, 2, 5),
(NULL, 4, 3, 1, 6, 2, 5),
(NULL, 4, 5, 6, 2, 3, 1),
(NULL, 4, 3, 1, 5, 6, 2);

我的格式可以生成如下:-

CREATE TABLE vote_orders
(
    id INT NOT NULL AUTO_INCREMENT,
    vote_id INT,
    vote_order INT,
    vote_candidate INT
);

INSERT INTO vote_orders (id, vote_id, vote_order, vote_candidate)
SELECT NULL, vote_id, 1, vote_candidate_a FROM votes
UNION
SELECT NULL, vote_id, 2, vote_candidate_b FROM votes
UNION
SELECT NULL, vote_id, 3, vote_candidate_c FROM votes
UNION
SELECT NULL, vote_id, 4, vote_candidate_d FROM votes
UNION
SELECT NULL, vote_id, 5, vote_candidate_e FROM votes
UNION
SELECT NULL, vote_id, 6, vote_candidate_f FROM votes;

然后您可以简单地使用以下内容来获得选票。这使用子查询来获得尚未使用的最高投票，然后将其与数据结合起来。

SELECT vote_candidate, COUNT(*)
FROM vote_orders a
INNER JOIN
(
    SELECT vote_id, MIN(vote_order) AS min_vote_order
    FROM vote_orders
    WHERE vote_candidate NOT IN (4,3,5)
    GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
INNER JOIN
(
    SELECT vote_id
    FROM vote_orders
    WHERE vote_candidate  = 4
    AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate

SQL fiddle 在这里:-

http://www.sqlfiddle.com/#!2/7d48c/10

混合解决方案(两全其美!):-

SELECT vote_candidate, COUNT(*)
FROM 
(
    SELECT vote_id, 1 AS vote_order, vote_candidate_a AS vote_candidate FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 2, vote_candidate_b FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 3, vote_candidate_c FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 4, vote_candidate_d FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 5, vote_candidate_e FROM votes WHERE vote_candidate_a = 4
    UNION
    SELECT vote_id, 6, vote_candidate_f FROM votes WHERE vote_candidate_a = 4
) a
INNER JOIN
(
    SELECT vote_id, MIN(vote_order) AS min_vote_order
    FROM 
    (
        SELECT vote_id, 2 AS vote_order, vote_candidate_b AS vote_candidate FROM votes
        UNION
        SELECT vote_id, 3, vote_candidate_c FROM votes
        UNION
        SELECT vote_id, 4, vote_candidate_d FROM votes
        UNION
        SELECT vote_id, 5, vote_candidate_e FROM votes
        UNION
        SELECT vote_id, 6, vote_candidate_f FROM votes
    ) a
    WHERE vote_candidate NOT IN (4,3,5)
    GROUP BY vote_id
) b
ON a.vote_id = b.vote_id
AND a.vote_order = b.min_vote_order
GROUP BY vote_candidate;