根据行中的其他值更新列

Consider the following trials table:

考虑以下试验表：

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);

Abstractly, this represents a series of trials in which two possible things, A and B, are tried. A and B each get a score.

抽象地说，这代表了一系列试验，其中尝试了两种可能的东西，A和B. A和B各得一分。

Later we add two columns winner and loser, both of the same datatype as name_A and name_B:

稍后我们添加两列赢家和输家，两者都是与name_A和name_B相同的数据类型：

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

For each trial, we want to fill in winner with whichever name corresponds to the higher score.

对于每个试验，我们希望使用与较高分数相对应的任何名称来填写获胜者。

For example, if a trial has

例如，如果试验有

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

then for that trial winner should be alice. Similarly, in this case loser should be populated with bob:

那么对于那个试验，获胜者应该是爱丽丝。同样，在这种情况下，输家应填充bob：

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

How does one UPDATE to set the winner and loser columns in the trials table properly?

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

---

Attempt:

尝试：

I considered doing this with a sub-query. Here is a sub-query which find both the winners and losers:

我考虑过使用子查询。这是一个查找赢家和输家的子查询：

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)

Trying to just get the winners, I did this:

试着赢得胜利者，我这样做了：

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;

but it does not work because the field temp.id is not recognized.

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

2 个解决方案

#1

You could make it without a subquery:

您可以在没有子查询的情况下创建它：

Query:

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;

Test:

Create table:

创建表格：

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);

Empty table:

空表：

SELECT * FROM test.trials;

Test data:

测试数据：

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 |
+----+--------+--------+---------+---------+

Adding winner and loser columns:

添加赢家和输家列：

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  |
+----+--------+--------+---------+---------+--------+-------+

Running the query:

运行查询：

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 |
+----+--------+--------+---------+---------+--------+-------+

You could make it even using IF on winner and loser. I used CASE because if there's a match between score_A and score_B, what do you want the query to do?. Add a new column match.

你甚至可以在赢家和输家上使用IF。我使用了CASE，因为如果score_A和score_B之间存在匹配，那么您希望查询做什么？添加新列匹配。

#2

UPDATE Trials
SET Winner =
    CASE
        WHEN score_A > score_B THEN name_A
        WHEN score_B > score_A THEN name_B
        ELSE NULL
        END