在MySQL中使用联合的适当分组

Question

我已经扫描了StackOverflow和互联网，寻找以下问题的答案，但我发现没有一个答案对我有用。这是我的问题。

我有两个查询，我想通过使用UNION组合起来。我成功地按照预期将它们组合在一起，但我无法让它们按照我想要的方式分组。我希望通过排序子查询来隐藏重分发值为0的重复行，以便在分组时对它们进行排序.(我知道糟糕的解释-我希望下面的图形演示能更好地解释它。我想要删除的行在右边用一个小箭头标记)。

我到底要怎么做？

+-----------+-------+---------------+----------------+----------------------------+
| CANDIDATE | VOTES | RANKED_CHOICE | REDISTRIBUTION | VOTES_AFTER_REDISTRIBUTION |
+-----------+-------+---------------+----------------+----------------------------+
|         1 |     8 |             0 |              0 |                          8 |
|         2 |     1 |             6 |             -1 |                          0 |
|         2 |     1 |             0 |              0 |                          1 | >
|         3 |     2 |             0 |              0 |                          2 |
|         4 |     4 |             0 |              0 |                          4 |
|         5 |     2 |             0 |              0 |                          2 |
|         6 |     3 |             0 |              0 |                          3 | >
|         6 |     3 |             0 |              1 |                          4 |
+-----------+-------+---------------+----------------+----------------------------+

-- The resulting table that's shown on the screen
SELECT vote_candidate candidate, original_votes votes, ranked_choice, redistribution, (original_votes + redistribution) votes_after_redistribution
FROM (
  -- Create the first table with original information
  SELECT c.vote_candidate, c.original_votes, '0' ranked_choice, '0' redistribution
  FROM (
    SELECT o.vote_candidate, COUNT(*) original_votes
    FROM vote_orders o
    WHERE o.vote_order = 1
    GROUP BY o.vote_candidate
  ) c
  GROUP BY c.vote_candidate
  -- Union a second table containing the second ranked choice of an eliminated candidate and the redistribution.
  -- This is done in two steps. In the first step we find out the ranking. In the second step we union the ranked
  -- candidate and its' redistribution with each other
  UNION
  SELECT vote_candidate, original_votes, ranked_choice, redistribution
  FROM ((
    SELECT vote_candidate, IFNULL(d.original_votes, 0) original_votes, IFNULL(COUNT(*), 0) ranked_choice, (0 - IFNULL(d.original_votes, 0)) redistribution
    FROM vote_orders a
    -- Get the second favored vote from each eliminated candidates ballots
    INNER JOIN (
      SELECT vote_id, c, MIN(minimum_vote)
      FROM (
        SELECT vote_id, vote_candidate c, COUNT(*) minimum_vote
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
      ) t1
      WHERE minimum_vote = (
        SELECT MIN(minimum_vote)
        FROM (
          SELECT COUNT(*) minimum_vote
          FROM vote_orders
          WHERE vote_order = 1
          GROUP BY vote_candidate
        ) t2
      )
      GROUP BY c
    ) b
    ON a.vote_id = b.vote_id
    -- Get the eliminated candidates votes at the beginning of this round
    LEFT OUTER JOIN
    (
      SELECT vote_candidate o, COUNT(*) original_votes
      FROM vote_orders
      WHERE vote_order = 1
      GROUP BY vote_candidate
    ) d
    ON a.vote_candidate = d.o
    GROUP BY vote_candidate
    ORDER BY redistribution DESC
    LIMIT 1
    -- Union the candidates redistribution
    UNION
    (
      SELECT vote_candidate, d.original_votes, '0' ranked_choice, (CASE
                                                                   WHEN IFNULL(d.original_votes, 0) = 0
                                                                   THEN (0 - IFNULL(d.original_votes, 0))
                                                                   ELSE (
                                                                     SELECT MIN(minimum_vote)
                                                                     FROM (
                                                                       SELECT vote_candidate c, COUNT(*) minimum_vote
                                                                       FROM vote_orders
                                                                       WHERE vote_order = 1
                                                                       GROUP BY vote_candidate
                                                                     ) t1
                                                                     WHERE minimum_vote = (
                                                                       SELECT MIN(minimum_vote)
                                                                       FROM (
                                                                         SELECT COUNT(*) minimum_vote
                                                                         FROM vote_orders
                                                                         WHERE vote_order = 1
                                                                         GROUP BY vote_candidate
                                                                       ) t2
                                                                     )
                                                                     GROUP BY c
                                                                   )
                                                                   END) redistribution
      FROM vote_orders a
      INNER JOIN (
        SELECT vote_id, MIN(minimum_vote)
        FROM (
          SELECT vote_id, COUNT(*) minimum_vote
          FROM vote_orders
          WHERE vote_order = 1
          GROUP BY vote_candidate
        ) t1
        WHERE minimum_vote = (
          SELECT MIN(minimum_vote)
          FROM (
            SELECT COUNT(*) minimum_vote
            FROM vote_orders
            WHERE vote_order = 1
            GROUP BY vote_candidate
          ) t2
        )
      ) b
      ON a.vote_id = b.vote_id
      LEFT OUTER JOIN
      (
        SELECT vote_candidate o, COUNT(*) original_votes
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
      ) d
      ON a.vote_candidate = d.o
      -- Determine which candidate to add the redistribution to
      WHERE vote_candidate = (
        SELECT IFNULL(COUNT(*), 0) ranked_choice
        FROM vote_orders a
        INNER JOIN (
          SELECT vote_id, c, MIN(minimum_vote)
          FROM (
            SELECT vote_id, vote_candidate c, COUNT(*) minimum_vote
            FROM vote_orders
            WHERE vote_order = 1
            GROUP BY vote_candidate
          ) t1
          WHERE minimum_vote = (
            SELECT MIN(minimum_vote)
            FROM (
              SELECT COUNT(*) minimum_vote
              FROM vote_orders
              WHERE vote_order = 1
              GROUP BY vote_candidate
            ) t2
          )
          GROUP BY c
        ) b
        ON a.vote_id = b.vote_id
      )
      GROUP BY vote_candidate
      ORDER BY redistribution DESC
      LIMIT 1
    )
  )) y    
) z
-- This is where the grouping fails on me
-- GROUP BY vote_candidate
ORDER BY vote_candidate ASC;

这是一个模式：

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,
                             PRIMARY KEY vote_id(vote_id)
);

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


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

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;

Answer 1

在现实中，你已经知道什么是vote_candidate和得票率是马上就好了。这是一个简单的查询，

Select vote_candidate, count(*)
From vote_orders
Where vote_order = 1
Group by vote_candidate

这是整个查询的基础，因为select中唯一缺少的东西是ranked_choice、重新分配和votes_after_redistribution (尽管这是从其他两列中计算出来的，所以这是可以忽略不计的)。因此，我建议使用您所做的所有工作来查找两个缺失的列。本质上，您应该将查询更改为子select，以便只查找缺少的列。

我更改了您的子选择，以只找到ranked_choice和重新分配的值，如果它们不是0。然后，我获取这些值(使用左外部联接)。如果子select中没有任何内容，那么我们将将该值默认为0。

上一次查询首先将所有值默认为0，然后返回非0行的信息。让我们跳过所有额外的工作。注意，我仍然建议清理子选择，特别是因为我删除了原始选票的点票。有些联接可能是额外的，因为您不再需要找到该信息。小提琴：http://sqlfiddle.com/#!2/1b0cb/51

-- The resulting table that's shown on the screen
SELECT v.vote_candidate candidate,
        count(*) votes,
        IfNull(z.ranked_choice, 0) ranked_choice,
        IfNull(z.redistribution, 0) redistribution,
        (count(*) + IfNull(z.redistribution, 0)) votes_after_redistribution
FROM vote_orders v left outer join

(

  -- Union a second table containing the second ranked choice of an eliminated candidate and the redistribution.
  -- This is done in two steps. In the first step we find out the ranking. In the second step we union the ranked
  -- candidate and its' redistribution with each other
  SELECT vote_candidate, ranked_choice, redistribution
  FROM ((
    SELECT  vote_candidate,
            IFNULL(COUNT(*), 0) ranked_choice,
            (0 - IFNULL(d.original_votes, 0)) redistribution
    FROM vote_orders a
    -- Get the second favored vote from each eliminated candidates ballots
    INNER JOIN (
        SELECT vote_id, vote_candidate c, MIN(minimum_vote)
        FROM (
          SELECT vote_id, vote_candidate, COUNT(*) minimum_vote
          FROM vote_orders
          WHERE vote_order = 1
          GROUP BY vote_candidate
        ) t2
    ) b
    ON a.vote_id = b.vote_id
    -- Get the eliminated candidates votes at the beginning of this round
    LEFT OUTER JOIN
    (
      SELECT vote_candidate o, COUNT(*) original_votes
      FROM vote_orders
      WHERE vote_order = 1
      GROUP BY vote_candidate
    ) d
    ON a.vote_candidate = d.o
    GROUP BY vote_candidate
    ORDER BY redistribution DESC
    LIMIT 1
    -- Union the candidates redistribution
    UNION
    (
      SELECT  vote_candidate,
              0 ranked_choice,
              (CASE
                   WHEN IFNULL(d.original_votes, 0) = 0
                   THEN (0 - IFNULL(d.original_votes, 0))
                   ELSE (
                          SELECT MIN(minimum_vote)
                                FROM (
                                      SELECT COUNT(*) minimum_vote
                                      FROM vote_orders
                                      WHERE vote_order = 1
                                      GROUP BY vote_candidate
                                      ) t2
                    )
                END) redistribution
      FROM vote_orders a
      INNER JOIN (
        SELECT vote_id, MIN(minimum_vote)
        FROM (
          SELECT vote_id, COUNT(*) minimum_vote
          FROM vote_orders
          WHERE vote_order = 1
          GROUP BY vote_candidate
        ) t1
        WHERE minimum_vote = (
          SELECT MIN(minimum_vote)
          FROM (
            SELECT COUNT(*) minimum_vote
            FROM vote_orders
            WHERE vote_order = 1
            GROUP BY vote_candidate
          ) t2
        )
      ) b
      ON a.vote_id = b.vote_id
      LEFT OUTER JOIN
      (
        SELECT vote_candidate o, COUNT(*) original_votes
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
      ) d
      ON a.vote_candidate = d.o
      -- Determine which candidate to add the redistribution to
      WHERE vote_candidate = (
        SELECT IFNULL(COUNT(*), 0) ranked_choice
        FROM vote_orders a
        INNER JOIN (
          SELECT vote_id, c, MIN(minimum_vote)
          FROM (
            SELECT vote_id, vote_candidate c, COUNT(*) minimum_vote
            FROM vote_orders
            WHERE vote_order = 1
            GROUP BY vote_candidate
          ) t1
          WHERE minimum_vote = (
            SELECT MIN(minimum_vote)
            FROM (
              SELECT COUNT(*) minimum_vote
              FROM vote_orders
              WHERE vote_order = 1
              GROUP BY vote_candidate
            ) t2
          )
          GROUP BY c
        ) b
        ON a.vote_id = b.vote_id
      )
      GROUP BY vote_candidate
      ORDER BY redistribution DESC
      LIMIT 1
    )
  )) y    
) z
on v.vote_candidate = z.vote_candidate
Where v.vote_order = 1
GROUP BY v.vote_candidate
ORDER BY v.vote_candidate ASC;