mysql - 根据行中的其他值更新列

考虑下面的trials表:

CREATE TABLE trials
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name_A VARCHAR(6),
name_B VARCHAR(6),
score_A INT,
score_B INT);

概括地说，这代表了一系列试验，其中尝试了两种可能的情况，即A和B。 A和B均获得分数。

稍后，我们添加两列winner和loser，它们的数据类型与name_A和name_B相同:

ALTER TABLE trials
ADD COLUMN winner VARCHAR(6),
ADD COLUMN loser VARCHAR(6);

对于每个试验，我们都希望使用对应于较高分数的名称来填充winner。

例如，如果一个审判有

╔════════╦════════╦═════════╦═════════╗
║ name_A ║ name_B ║ score_A ║ score_B ║
╠════════╬════════╬═════════╬═════════╣
║ alice  ║ bob    ║ 10      ║ 5       ║
╚════════╩════════╩═════════╩═════════╝

那么该试验的winner应该是alice。同样，在这种情况下，loser应该填充为bob:

╔════════╦════════╦═════════╦═════════╦════════╦═══════╗
║ name_A ║ name_B ║ score_A ║ score_B ║ winner ║ loser ║
╠════════╬════════╬═════════╬═════════╬════════╬═══════╣
║ alice  ║ bob    ║ 10      ║ 5       ║ alice  ║ bob   ║
╚════════╩════════╩═════════╩═════════╩════════╩═══════╝

一个UPDATE如何正确设置trials表中的赢家和输家列？

尝试:

我考虑使用子查询来执行此操作。
这是一个子查询，可以找到赢家和输家:

SELECT id, name_A AS winner, name_B AS loser
  FROM trials
  WHERE score_A > score_B
  UNION
  SELECT id, name_B AS winner, name_A AS loser
  FROM trials
  WHERE score_B > score_A)

为了获得胜利者，我做到了:

UPDATE trials SET winner=(
  SELECT id, winner from (
    SELECT id, name_A AS winner
      FROM trials
      WHERE score_A > score_B
    UNION
    SELECT id, name_B AS winner
      FROM trials
      WHERE score_B > score_A) AS temp
  )
  WHERE temp.id = trials.id;

但是它不起作用，因为无法识别temp.id字段。

最佳答案

您可以在没有子查询的情况下实现:

询问:

UPDATE test.trials AS t
SET t.winner=CASE WHEN t.score_A > t.score_B THEN t.name_A
                  WHEN t.score_A < t.score_B THEN t.name_B
             ELSE NULL END,
    t.loser=CASE WHEN t.score_A > t.score_B THEN t.name_B
                  WHEN t.score_A < t.score_B THEN t.name_A
             ELSE NULL END;

测试:

创建表:

CREATE TABLE trials
    (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name_A VARCHAR(6),
    name_B VARCHAR(6),
    score_A INT,
    score_B INT);

空表:

SELECT * FROM test.trials;

测试数据:

INSERT INTO test.trials (id, name_A, name_B, score_A, score_B) VALUES ('1', 'alice', 'bob', '10', '5');
INSERT INTO test.trials (id, name_A, name_B, score_A, score_B) VALUES ('2', 'onare', 'some', '5', '11');

SELECT * FROM test.trials;

+----+--------+--------+---------+---------+
| id | name_A | name_B | score_A | score_B |
+----+--------+--------+---------+---------+
|  1 | alice  | bob    |      10 |       5 |
|  2 | onare  | some   |       5 |      11 |
+----+--------+--------+---------+---------+

添加winner和loser列:

ALTER TABLE test.trials
    ADD COLUMN winner VARCHAR(10) NULL COMMENT '' AFTER score_B,
    ADD COLUMN loser VARCHAR(10) NULL COMMENT '' AFTER winner;

SELECT * FROM test.trials;

+----+--------+--------+---------+---------+--------+-------+
| id | name_A | name_B | score_A | score_B | winner | loser |
+----+--------+--------+---------+---------+--------+-------+
|  1 | alice  | bob    |      10 |       5 | NULL   | NULL  |
|  2 | onare  | some   |       5 |      11 | NULL   | NULL  |
+----+--------+--------+---------+---------+--------+-------+

运行查询:

UPDATE test.trials AS t
    SET t.winner=CASE WHEN t.score_A > t.score_B THEN t.name_A
    WHEN t.score_A < t.score_B THEN t.name_B
    ELSE NULL END,
    t.loser=CASE WHEN t.score_A > t.score_B THEN t.name_B
    WHEN t.score_A < t.score_B THEN t.name_A
    ELSE NULL END;


SELECT * FROM test.trials;

+----+--------+--------+---------+---------+--------+-------+
| id | name_A | name_B | score_A | score_B | winner | loser |
+----+--------+--------+---------+---------+--------+-------+
|  1 | alice  | bob    |      10 |       5 | alice  | bob   |
|  2 | onare  | some   |       5 |      11 | some   | onare |
+----+--------+--------+---------+---------+--------+-------+

您甚至可以在o​​jit_code和IF上使用winner做到这一点。我使用loser是因为，如果CASE和score_A之间存在匹配项，您希望查询做什么？添加新列score_B。

关于mysql - 根据行中的其他值更新列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32030836/