将表与MySQL中相同的列组合起来

Question

我试图复制这个问题Combine 2 tables with identical columns给出的场景，但是我遇到了一些严重的问题。基本设置是这样的。我有两个列相同但内容不同的表。

MASTER TABLE
    +----------------+---------------+----------------+--------------------------------------+
    | VOTE_CANDIDATE | SURPLUS_RATIO | ORIGINAL_VOTES | SURPLUS_REDISTRIBUTION_TO_CANDIDATES |
    +----------------+---------------+----------------+--------------------------------------+
    |              1 |         0.125 |              8 |                                   -1 |
    |              2 |         0.125 |              1 |                                   -1 |
    |              3 |         0.125 |              2 |                                   -1 |
    |              4 |         0.125 |              4 |                                   -1 |
    |              5 |         0.125 |              2 |                                   -1 |
    |              6 |         0.125 |              3 |                                   -1 |
    +----------------+---------------+----------------+--------------------------------------+

SLAVE TABLE
    +----------------+---------------+----------------+--------------------------------------+
    | VOTE_CANDIDATE | SURPLUS_RATIO | ORIGINAL_VOTES | SURPLUS_REDISTRIBUTION_TO_CANDIDATES |
    +----------------+---------------+----------------+--------------------------------------+
    |              2 |         0.125 |              1 |                                0.125 |
    |              3 |         0.125 |              2 |                                 0.25 |
    |              4 |         0.125 |              4 |                                  0.5 |
    |              5 |         0.125 |              2 |                                 0.25 |
    +----------------+---------------+----------------+--------------------------------------+

我想将它们组合成一个表，即用从表中的相应行替换2,3,4,5行的SURPLUS_REDISTRIBUTION_TO_CANDIDATES。我已经设法解决了这个问题(最后一行由于某种原因被排除了--我不知道为什么)。

COMBINED TABLE
    +----------------+---------------+----------------+--------------------------------------+
    | VOTE_CANDIDATE | SURPLUS_RATIO | ORIGINAL_VOTES | SURPLUS_REDISTRIBUTION_TO_CANDIDATES |
    +----------------+---------------+----------------+--------------------------------------+
    |              1 |         0.125 |            120 |                                   -1 |
    |              2 |         0.125 |              1 |                                0.125 |
    |              3 |         0.125 |              2 |                                 0.25 |
    |              4 |         0.125 |              4 |                                  0.5 |
    |              5 |         0.125 |              2 |                                 0.25 |
    +----------------+---------------+----------------+--------------------------------------+

这与我想要的非常接近，尽管第1行的ORIGINAL_VOTES应该是8，而不是120。似乎查询总结了所有的选票，而不是vote_order = 1？为什么？更多的选票--我如何确保最后一行(6)不会从结果中删除？

我的查询很讨厌，但看起来如下：

SELECT vote_candidate, (
  SELECT (
    (MAX(votes_above_the_threshold) - (
      SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
      FROM votes
    )) / MAX(votes_above_the_threshold)
  ) ratio
  FROM (
    SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
    FROM vote_orders
    WHERE vote_order = 1
    GROUP BY vote_candidate
    HAVING votes_above_the_threshold >= (
      SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
      FROM votes
    )
  ) t
  WHERE votes_above_the_threshold = (
    SELECT MAX(votes_above_the_threshold)
    FROM vote_orders
  )
) surplus_ratio, COUNT(*) original_votes, CASE 
  WHEN (
    SELECT MAX(votes_above_the_threshold) votes
    FROM (
      SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
        HAVING votes_above_the_threshold >= (
          SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
          FROM votes
        )
      ) t
      WHERE votes_above_the_threshold = (
        SELECT MAX(votes_above_the_threshold)
        FROM vote_orders
      )
    ) > (
      SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
      FROM votes
    )
  THEN (
      SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
      FROM votes
    ) - (SELECT MAX(votes_above_the_threshold) votes
    FROM (
      SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
        HAVING votes_above_the_threshold >= (
          SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
          FROM votes
        )
      ) t
      WHERE votes_above_the_threshold = (
        SELECT MAX(votes_above_the_threshold)
        FROM vote_orders
      )
    )
  ELSE 0
END surplus_redistribution_to_candidates
FROM vote_orders
UNION
SELECT vote_candidate, (
  SELECT (
    (MAX(votes_above_the_threshold) - (
      SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
      FROM votes
    )) / MAX(votes_above_the_threshold)
  ) ratio
  FROM (
    SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
    FROM vote_orders
    WHERE vote_order = 1
    GROUP BY vote_candidate
    HAVING votes_above_the_threshold >= (
      SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
      FROM votes
    )
  ) t
  WHERE votes_above_the_threshold = (
    SELECT MAX(votes_above_the_threshold)
    FROM vote_orders
  )
) surplus_ratio, COUNT(*) original_votes, (
  ROUND((COUNT(*) * (
    SELECT (
      (MAX(votes_above_the_threshold) - (
        SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
        FROM votes
      )) / MAX(votes_above_the_threshold)
    ) ratio
    FROM (
      SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
      FROM vote_orders
      WHERE vote_order = 1
      GROUP BY vote_candidate
      HAVING votes_above_the_threshold >= (
        SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
        FROM votes
      )
    ) t
    WHERE votes_above_the_threshold = (
      SELECT MAX(votes_above_the_threshold)
      FROM vote_orders
    )
  )), 3)
) surplus_redistribution_to_candidates
FROM vote_orders b
WHERE vote_order = 1
AND vote_candidate IN ((
  SELECT vote_candidate
  FROM vote_orders a
  INNER JOIN (
    SELECT vote_id, MIN(vote_order) AS min_vote_order
    FROM vote_orders
    WHERE vote_candidate NOT IN ((
      SELECT vote_candidate
      FROM (
        SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
        HAVING (
          votes_above_the_threshold >= (
            SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
            FROM votes
          )
          OR (
            votes_above_the_threshold >= (
              SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
              FROM votes
            )
            AND votes_above_the_threshold = 0
          )
          OR (
            votes_above_the_threshold = 0
          )
        )
      ) t
    ))
    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 = (
      SELECT vote_candidate
      FROM (
        SELECT vote_candidate vote_candidate, COUNT(*) votes_above_the_threshold
        FROM vote_orders
        WHERE vote_order = 1
        GROUP BY vote_candidate
        HAVING votes_above_the_threshold >= (
          SELECT FLOOR((COUNT(*) / (2 + 1)) + 1) threshold
          FROM votes
        )
      ) t
      WHERE votes_above_the_threshold >= (
        SELECT MAX(votes_above_the_threshold)
        FROM vote_orders
      )
    )
    AND vote_order = 1
  ) c
  ON a.vote_id = c.vote_id
  GROUP BY vote_candidate
))
GROUP BY vote_candidate;

我正在MySQL 5.0.83上运行这个程序。

有什么建议吗？

Answer 1

除非我严重误解了你的问题，否则我认为你把问题搞得太复杂了。这就是你要找的吗？(显然，将列/表名更改为所需)

UPDATE Master m
JOIN Slave s ON m.VOTE_CANDIDATE = s.VOTE_CANDIDATE
SET m.SURPLUS_REDIST = s.SURPLUS_REDIST

编辑:这做了一些假设，但将显示这些更改而不实际执行更新。另一种方法是将母版表复制到临时表中，并更新临时表，然后从其中选择*。

SELECT * FROM Master m
WHERE VOTE_CANDIDATE NOT IN (
    SELECT VOTE_CANDIDATE FROM Slave
)
UNION 
SELECT * FROM Slave