mysql - 迭代表并根据 MySQL 中的值执行逻辑

Question

我为此使用了一个存储过程，显然下面不是我的确切代码，而是一个经过高度修剪的版本来表达这个想法。请记住，我实际上对此临时表进行了很多过滤，因此向 CREATE TABLE 查询添加任何内容都会比任何事情都更麻烦。

BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS filtered_results AS (SELECT * FROM users);
  # Numerous filters removing stuff from the table.
END

出于这个问题的目的，我们可以说表结构非常简单，如下所示:

user_id INT(11) AUTO_INCREMENT PRIMARY
name VARCHAR(30) NOT NULL

现在假设我有一个具有以下结构的问题表:

question_id INT(11) AUTO_INCREMENT PRIMARY
user_id INT(11) 
text VARCHAR(40)

现在假设我想迭代该表并检查其他一些表以过滤没有至少 4 个问题的用户。所以像这样:

for each row in filtered_results
    if count(question_id) < 4 where user_id = row.user_id in questions
        delete row
    end if
end loop

Answer 1

经过一些讨论和您的最后评论，我想我想出了一个比运行游标更优雅的解决方案(正如我之前在评论中提到的)。

您需要根据 to_be_deleted_table 与 questions 表的 LEFT JOIN 对结果运行删除命令。我考虑到您的 to_be_deleted_table 上有 user_id。

考虑这个示例:

create table filtered_results (
    user_id integer,
    name varchar(10)
);
insert into filtered_results values
 (1, 'Bob'), (2, 'Sally'), (3, 'Cheer'), (4, 'Sid'), (5, 'Simon'), 
 (6, 'Lua'), (7, 'Liv'), (8, 'Taylor'), (9, 'Jay'), (10, 'Mike');

create table questions (
    question_id integer,
    user_id integer
);

insert into questions values 
 (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), 
 (6, 2), (7, 2), 
 (8, 3), (9, 3), (10, 3), (11, 3), 
 (12, 4), (13, 4), (14, 4), (15, 4), 
 (16, 5), (17, 5), (18, 5), (19, 5), 
 (20, 6), (21, 6), (22, 6), (23, 6), 
 (24, 7), (24, 7), (24, 7), (24, 7);

此示例包含您所说的所有内容、有 4 个(或更多问题)的用户、有少于 4 个问题的用户以及根本没有问题的用户。

删除没有至少 4 个问题的用户的查询将是:

delete 
  from filtered_results
 where user_id in 
     (select user_id
        from (select f.user_id
                from filtered_results f
                  LEFT JOIN questions q ON (f.user_id = q.user_id)
               group by f.user_id
               having count(*) < 4) a
     );

您一定想知道为什么我有两个子选择，那是因为您需要欺骗编译器，以便可以从正在执行选择的同一个表中删除行，在本例中为filtered_results。

如果您想根据过滤表中的 user_id 从另一个表中删除，只需执行以下操作:

delete 
  from users
 where user_id in 
     (select f.user_id
        from filtered_results f
          LEFT JOIN questions q ON (f.user_id = q.user_id)
       group by f.user_id
      having count(*) < 4
     );

请记住，如果是这种情况，您不需要创建 filtered_results，只需使用在我展示的第一个案例中将其创建为子查询的查询即可。

希望有帮助。

如果你想看看它的工作原理，请看一下这个 SQLFiddle