sql - 选择一个变量

Question

我有很多命令，例如:

DELETE FROM table1 WHERE user_id IN (SELECT id FROM users WHERE email LIKE 'abc%');
DELETE FROM table2 WHERE user_id IN (SELECT id FROM users WHERE email LIKE 'abc%');
DELETE FROM table3 WHERE user_id IN (SELECT id FROM users WHERE email LIKE 'abc%');

如何通过提取变量/列表/数组/集合来优化它？

SELECT id FROM users WHERE email LIKE 'abc%'

Answer 1

如果您的目标是只写一次删除哪个用户的条件，您可以使用数据修改 CTE:

with user_list as (
   select id
   from users
   where email like 'abc%'
), delete_1 as (
   delete from table_1 where user_id in (select id from user_list)
), delete_1  as (
   delete from table_2 where user_id in (select id from user_list)
) 
delete from table_3 where user_id in (select id from user_list);

您仍然需要为每次删除重复select ...，但是关于需要删除的内容的条件在您的语句中只有一次。