sql - SQL-计算一列与另一列匹配的行(对)

Question

我在一个网站上有一个投票系统，每个用户都可以投票给一对用户（女性和男性）取胜。

选票存储在下面的表格中。

voter_id |  voted_for | cell
90       |  30        | f
90       |  68        | m
42       |  30        | f
42       |  68        | m
111      |  74        | f
111      |  20        | m
45       |  120       | f
16       |  90        | f
16       |  135       | m
122      |  30        | f
122      |  68        | m
45       |  116       | m
46       |  30        | m
46       |  121       | f

现在，我要计算每对有多少用户投票。
例如。 '对30和68对有3票，对74和20对有1票，'对120和116有1票''，依此类推...

我有一个SQL查询，该查询会计算voted_for中提到的每个用户，但这不包括配对事物。它将为用户30给我4票，但用户30虽然在30＆121对中。

SELECT voted_for, count(*) AS count
FROM votes
GROUP BY voted_for
ORDER BY count desc

Answer 1

首先像这样转换表：

| voter_id | voted_for_f | voted_for_m |
|----------|-------------|-------------|
|       16 |          90 |         135 |
|       42 |          30 |          68 |
|       45 |         120 |         116 |
|       46 |          30 |         121 |
|       90 |          30 |          68 |
|      111 |          74 |          20 |
|      122 |          30 |          68 |

您可以使用以下查询来执行此操作，该查询将表与自身连接：

SELECT f.voter_id,
       f.voted_for AS voted_for_f,
       m.voted_for AS voted_for_m
FROM votes AS f
JOIN votes AS m ON f.voter_id = m.voter_id
               AND f.cell = 'f'
               AND m.cell = 'm'

然后，您可以使用此查询来依靠它：

SELECT f.voted_for AS voted_for_f,
       m.voted_for AS voted_for_m,
       COUNT(*) AS number_of_votes
FROM votes AS f
JOIN votes AS m ON f.voter_id = m.voter_id
               AND f.cell = 'f'
               AND m.cell = 'm'
GROUP BY voted_for_f,
         voted_for_m