mysql - 如何应用规则根据条件和分组数据更新字段中的记录

Question

我正在尝试在 sql 中执行此任务。但我无法得出有助于获得此输出的逻辑。

标签： mysqlsqlsubquerywindow-functions

Answer 1

It looks like you want to bring the name of the player whose code is "x" for each id. If so, one option uses window functions:

select id, playercode, playername,
    max(case when playercode = 'x' then playername end) over(partition by id) requiredoutput
from mytable

If your database does not support window functions, one option uses a correlated subquery. Assuming no duplicates on (id, playercode):

select id, playercode, playername,
    (
        select t1.playername 
        from mytable t1 
        where t1.id = t.id and t1.playercode = 'x'
    ) requiredoutput
from mytable t