SQL:计算(并填充?)排序结果之间的差异

Question

我正在使用MariaDB（根据我的有限概念，MySQL的一个替代品）来支持一个小的选举应用程序。

我为候选人存储的一件事是"领先优势"，即他们的多数票是多少。如果候选人a赢得6票，候选人B赢得12票，候选人C赢得7票，那么这些值分别为0、5、0。

但现在我有了一个新的大数据集，我知道投票和投票情况，但没有"领先优势"。有数百名候选人，所以我需要计算一下。这就是我在英语中的意思：

1. 按骑行对每个人进行分类
2. 然后按票数排序
3. 减去（排名第一的候选人的选票-排名第二的候选人的票数）并将结果存储在排名第一的"领先优势"列中
4. 其他人都得到0。或者将其留空

下面是一个完整表格的例子，展示了我的目标。它和真实数据之间的唯一区别是，我还没有计算出"领先优势"。

MariaDB [databasename]> SELECT * FROM `demo_candidates` ORDER BY `riding` ASC , `votes` DESC;
+--------+-----------+-----------+---------+-------+-------+---------------+---------+
| id     | riding    | lname     | fname   | party | votes | leadingmargin | percent |
+--------+-----------+-----------+---------+-------+-------+---------------+---------+
|      1 |         1 | Redford   | Richard |     1 |    92 |            50 |   57.14 |
|      4 |         1 | Pelford   | Paul    |     4 |    42 |             0 |   26.09 |
|      3 |         1 | Yeltmate  | Yoris   |     3 |    16 |             0 |    9.94 |
|      2 |         1 | Gint      | Ginny   |     2 |    11 |             0 |    6.83 |
|      6 |         2 | Gelford   | Gippy   |     2 |    99 |            16 |   44.59 |
|      5 |         2 | Roberts   | Roy     |     1 |    83 |             0 |   37.39 |
|      8 |         2 | Peg       | Porkay  |     4 |    28 |             0 |   12.61 |
|      7 |         2 | Yavin     | Yordy   |     3 |    12 |             0 |    5.41 |
etc..
+--------+-----------+-----------+---------+-------+-------+---------------+---------+
20 rows in set (0.00 sec)

Answer 1

此查询仅为获胜者设置领先优势。如果您将leadingmargin的DEFAULT值设置为0，或者运行另一个查询将其设置为0

UPDATE demo_candidates t1
LEFT JOIN demo_candidates t2
  ON t2.riding = t1.riding
  AND t2.votes > t1.votes
JOIN demo_candidates t3
  ON t3.riding = t1.riding
  AND t3.votes < t1.votes
LEFT JOIN demo_candidates t4
  ON t4.riding = t3.riding
  AND t4.id <> t1.id
  AND t4.votes > t3.votes
SET t1.leadingmargin = t1.votes - t3.votes
WHERE t2.id IS NULL AND t4.id IS NULL

如果双方势均力敌，或者只有一名候选人，则不会确定领先优势。

更新：

这个也设置了0的：

UPDATE demo_candidates t1
LEFT JOIN demo_candidates t2
  ON t2.riding = t1.riding
  AND t2.votes > t1.votes
LEFT JOIN demo_candidates t3
  ON t3.riding = t1.riding
  AND t3.votes < t1.votes
LEFT JOIN demo_candidates t4
  ON t4.riding = t3.riding
  AND t4.id <> t1.id
  AND t4.votes > t3.votes
SET t1.leadingmargin = IF(t2.id IS NULL AND t4.id IS NULL, t1.votes - t3.votes, 0)