mysql - 合并 MySQL 中两个表之间的一列

Question

这是 question 的延续我上周问过。它涉及来自两个不同表的表列的组合。

请考虑这个例子。

我有一个包含这些值的表 (vote_orders a):

+----------------+---------------------+----------------+
| VOTE_CANDIDATE | RANKED_CHOICE_VOTES | ORIGINAL_VOTES |
+----------------+---------------------+----------------+
|              2 |                   4 |              0 |
|              3 |                   1 |              0 |
|              4 |                   2 |              0 |
|              5 |                   1 |              0 |
+----------------+---------------------+----------------+

SELECT vote_candidate, COUNT(*) ranked_choice_votes, 0 original_votes
FROM vote_orders a
INNER JOIN
(
  SELECT vote_id, MIN(vote_order) AS min_vote_order
  FROM vote_orders
  WHERE vote_candidate NOT IN (1,6)
  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 = 1
  AND vote_order = 1
) c
ON a.vote_id = c.vote_id
GROUP BY vote_candidate;

我有另一个包含这些值的表 (vote_orders):

+----------------+
| ORIGINAL_VOTES |
+----------------+
|              1 |
|              2 |
|              4 |
|              2 |
+----------------+

SELECT COUNT(*) original_votes
FROM vote_orders
WHERE vote_order = 1
AND vote_candidate NOT IN (1,6)
GROUP BY vote_candidate;

现在我想合并这两个表，使结果表看起来像这样(注意 ORIGINAL_VOTES 现在包含 VOTE_ORDERS.ORIGINAL_VOTES [第二个查询] 内容):

+----------------+---------------------+----------------+
| VOTE_CANDIDATE | RANKED_CHOICE_VOTES | ORIGINAL_VOTES |
+----------------+---------------------+----------------+
|              2 |                   4 |              1 |
|              3 |                   1 |              2 |
|              4 |                   2 |              4 |
|              5 |                   1 |              2 |
+----------------+---------------------+----------------+

我猜它需要 UNION 和 JOIN 的高级组合，但我无法掌握如何将它们组合起来以获得此结果。您可以在此 fiddle 中进行试验.对于如何解决此问题的任何想法，我将不胜感激。

Answer 1

啊，我想我现在明白你想要什么了。

可能只是添加另一个子查询来获取每个候选人的主要投票数。然后 LEFT OUTER JOIN 这与你在 vote_candidate 字段上的其余结果。

SELECT a.vote_candidate, COUNT(*) ranked_choice_votes, d.original_votes
FROM vote_orders a
INNER JOIN
(
  SELECT vote_id, MIN(vote_order) AS min_vote_order
  FROM vote_orders
  WHERE vote_candidate NOT IN (1,6)
  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 = 1
  AND vote_order = 1
) c
ON a.vote_id = c.vote_id
LEFT OUTER JOIN
(
    SELECT vote_candidate, COUNT(*) AS original_votes
    FROM vote_orders 
    WHERE vote_order = 1
    GROUP BY vote_candidate
) d
ON a.vote_candidate = d.vote_candidate
GROUP BY vote_candidate;