mysql - 删除重复项，从组中留下至少一个具有最高参数的项

Question

我有以下架构:

+--+------+-----+----+|id|device|token|cash|+--+------+-----+----+

column device is unique and token is not unique and null by default.

What i want to achieve is to set all duplicate token values to default (null) leaving only one with highest cash. If duplicates have same cash leave first one.

I have heard about cursor, but it seems that it can be done with usual query.

I have tried following SELECT only to see if im right about my thought how to achieve this, but it seems im wrong.

SELECT 
    *
FROM
    db.table
WHERE
    db.table.token NOT IN (SELECT 
            *
        FROM
            (
            SELECT DISTINCT
                MAX(db.table.balance)
            FROM
                db.table
            GROUP BY db.table.balance) temp
            )

例如:查询后的这张表

+-----+---------+--------+-------+| id  |  device | token  |   cash|+-----+---------+--------+-------+| 1   | dev_1   | tkn_1  |  3    || 2   | dev_2   | tkn_1  |  10   || 3   | dev_3   | tkn_2  |  10   || 4   | dev_4   | tkn_2  |  14   || 5   | dev_5   | tkn_3  |  10   || 6   | dev_6   | null   |  10   || 7   | dev_7   | null   |  10   || 8   | dev_8   | tkn_4  |  11   || 8   | dev_8   | tkn_4  |  11   || 8   | dev_8   | tkn_5  |  11   |+-----+---------+--------+-------+

应该是:

+-----+---------+--------+-------+| id  |  device | token  |   cash|+-----+---------+--------+-------+| 1   | dev_1   | null   |  3    || 2   | dev_2   | tkn_1  |  10   || 3   | dev_3   | null   |  10   || 4   | dev_4   | tkn_2  |  14   || 5   | dev_5   | tkn_3  |  10   || 6   | dev_6   | null   |  10   || 7   | dev_7   | null   |  10   || 8   | dev_8   | tkn_4  |  11   || 8   | dev_8   | null   |  11   || 8   | dev_8   | tkn_5  |  15   |+-----+---------+--------+-------+

提前致谢:)

Answer 1

尝试使用 EXISTS 子查询:

UPDATE yourTable t1
SET token = NULL
WHERE EXISTS (SELECT 1 FROM (SELECT * FROM yourTable) t2
              WHERE t2.token = t1.token AND
                    t2.cash > t1.cash);

Demo

请注意，此答案假设具有相同最高现金金额的两个代币记录永远不会存在平局。