mysql查询两张表的同一列_在MySQL中的两个表之间合并一列

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

Please consider this example.

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;

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;

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 |

+----------------+---------------------+----------------+

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.

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;