如果Mysql中计算字段为0，则从结果集中删除记录 [英] Remove record from result set if a computed field is 0 in Mysql

问题描述

假设我有一个像这样的 MySQL 查询:

Suppose I have a MySQL query like so:

SET @group = 0;
SELECT Player_Id, IF(New_Group, @group:=@group + 1, @group) AS Group_Id
FROM [table]
ORDER BY Player_Id;

这会给我一个 Player_Ids 和计算的 Group_Ids(它们是数字)的列表.如果查询结果中的 Group_Id 为 0，我想删除任何记录.任何人都可以提供帮助，因为我的 SQL 技能很少.

This will give me a list of Player_Ids and computed Group_Ids (which are numbers). I want to remove any record if the Group_Id is 0 in my query result. Can anyone help as my SQL skills are minimal.

推荐答案

这应该可以解决您的问题:

That's should solve your problem:

SET @group = 0;
DELETE FROM [TABLE] WHERE Player_Id IN  (
    SELECT u.Player_Id FROM (
        SELECT
            Player_Id,
            IF(New_Group, @group:=@group + 1, @group) AS Group_Id
        FROM [TABLE]
        ORDER BY Player_Id
    ) AS u
    WHERE
        u.Group_Id = 0
);

这篇关于如果Mysql中计算字段为0，则从结果集中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！