sql - 在多个查询中使用一个结果集

Question

我想为一个相当大的基础创建一个小备份(这样开发人员可以下载 ~1-2gb 而不是 15gb)。

为了做到这一点，我复制了基础并运行了一些截断一些表(日志等)的脚本，我想删除一些用户(总是相同的用户)以外的数据。

现在我有这个:

-- delete order details not in test accounts
DELETE FROM order_details WHERE Album_ID NOT IN (
    SELECT Album_ID FROM albums WHERE User_ID IN (
        SELECT User_ID FROM users
            WHERE Email_ID LIKE '%@xxx.com'
            OR Email_ID LIKE '%@yyy.com'
            OR Email_ID LIKE '%@zzz.com'
    )
)
DELETE FROM orders WHERE User_ID NOT IN (
    SELECT User_ID FROM users
        WHERE Email_ID LIKE '%@xxx.com'
        OR Email_ID LIKE '%@yyy.com'
        OR Email_ID LIKE '%@zzz.com'
)

-- delete albums not in test accounts
DELETE FROM albums WHERE User_ID NOT IN (
    SELECT User_ID FROM users
        WHERE Email_ID LIKE '%@xxx.com'
        OR Email_ID LIKE '%@yyy.com'
        OR Email_ID LIKE '%@zzz.com'
)
-- snip a few more of the same

如您所见，我总是使用相同的 SELECT User_ID FROM users WHERE Email_ID LIKE '%@xxx.com' OR Email_ID LIKE '%@yyy.com' OR... 子-多处查询。

你会怎么做才能避免重复自己？

谢谢!

Answer 1

尝试将值插入临时变量并在所有 where 条件下使用相同的值。

DECLARE @table as TABLE(User_ID  Nvarchar(50))

INSERT INTO @table (User_ID)
SELECT User_ID FROM users
        WHERE Email_ID LIKE '%@xxx.com'
        OR Email_ID LIKE '%@yyy.com'
        OR Email_ID LIKE '%@zzz.com'

---

DELETE FROM order_details WHERE Album_ID NOT IN (
    SELECT Album_ID FROM albums WHERE User_ID IN (
        SELECT User_ID FROM @table))