mysql - SQL:计算(并填充？)有序结果之间的差异

Question

我正在使用 MariaDB(根据我有限的概念，它是 MySQL 的直接替代品)来支持一个小的选举应用程序。

我与候选人一起存储的东西之一是“leadingmargin”——即他们的复数是多少。如果候选人A获得6票，候选人B获得12票，候选人C获得7票，则值为0、5、0。

但现在我有了一个大的新数据集，我知道其中的选票和选票，但对“leadingmargin”一无所知。有几百个候选人，所以我需要计算这个。这就是我用英语的意思:

1. 按骑行对每个人进行排序。
2. 然后按投票排序。
3. 减去(第一名候选人的票 - 第二名候选人的票)并将结果存储在第一名的“leading margin”列中。
4. 其他人都得到 0。或者将其保留为 NULL。

这是一个完整的表格示例，用于展示我的目的。它与真实数据之间的唯一区别是我还没有计算“leadingmargin”。

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

此查询仅为获胜者设置领先 margin 。如果您将 leadingmargin 的默认值设置为 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

如果出现平局或只有一名候选人，则不会设置 leadingmargin。

更新:

这个也设置了 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)