mysql - 无别名删除

我想从表game中删除​​重复项。存在具有相同playerId和timeParameter的条目，而具有最低gameId的条目应保留。

我可以使用以下查询条目：

select a.`gameId` from `game` a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`playerId`, b.`timeParameter`
    having m = a.`gameId`);

但是我不能在delete语句中使用alis a：

delete from `game` a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`playerId`, b.`timeParameter`
    having m = a.`gameId`);

获取语​​法错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MariaDB server version for the right syntax to use near 'a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`' at line 1

该错误告诉我什么，但我知道我不能在delete语句中使用别名。

有解决方案吗？

最佳答案

您可以进行子查询并创建一个派生表

delete from game where gameId IN
(
select gameId from(
select a.`gameId` from `game` a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`playerId`, b.`timeParameter`
    having m = a.`gameId`)
)tmp
)

关于mysql - 无别名删除，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42021295/