在MySQL中的两个表之间合并一列 [英] Combining one column between two tables in MySQL

问题描述

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

This is a continuation of a question I asked last week. It concerns the combination of table columns from two different tables.

请考虑以下示例.

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

I have a table (vote_orders a) containing these values:

+----------------+---------------------+----------------+
| 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):

I have another table (vote_orders) containing these values:

+----------------+
| 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 [第二次查询]内容):

Now I want to combine these two tables so that the resulting table looks like this (notice that ORIGINAL_VOTES now contains VOTE_ORDERS.ORIGINAL_VOTES [second queries] content):

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

我猜想它需要UNION和JOIN的高级组合，但是我只是无法掌握如何将它们组合以获得结果.您可以在此小提琴中进行实验. 对于如何解决此问题的任何想法，我将深表感谢.

I guess that it requires an advanced combination of UNION and JOIN but I just can't get a grip on how to combine these to get this result. You can experiment with it in this fiddle. I would be deeply grateful for any ideas on how to solve this.

推荐答案

啊，想想我现在明白了你想要的东西.

Ah, think I see what you wanted now.

可能只需添加另一个子查询即可获取每个候选人的主要投票数.然后，在"voice_candidate"字段中将其余结果与其余结果相对应.

Possibly just add another sub query that gets the number of primary votes for each candidate. Then LEFT OUTER JOIN this against the rest of your results ON the vote_candidate field.

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;

这篇关于在MySQL中的两个表之间合并一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！