将结果集用于多个查询

Question

我希望创建一个较小的备份，而不是传输 15GB 的大型数据库给开发者（大约只有 1-2GB）。

为了实现这个目标，我复制了数据库，并运行了一些脚本来截断一些表格（如日志等），并且我想删除除一些用户（总是相同的用户）之外的所有数据。

现在我的做法如下：

-- 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 '[e-mail]'
            OR Email_ID LIKE '[e-mail]'
            OR Email_ID LIKE '[e-mail]'
    )
)
DELETE FROM orders WHERE User_ID NOT IN (
    SELECT User_ID FROM users
        WHERE Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
)

-- delete albums not in test accounts
DELETE FROM albums WHERE User_ID NOT IN (
    SELECT User_ID FROM users
        WHERE Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
)
-- snip a few more of the same

你会发现，我在很多地方都用了同样的SELECT User_ID FROM users WHERE Email_ID LIKE '[e-mail]' OR Email_ID LIKE '[e-mail]' OR...子查询。

你该如何不重复自己地实现这一点？

谢谢！

- thomasb

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 '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'

---

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

- TechDo

Answer 2

使用临时表。

IF OBJECT_ID('tempdb.dbo.#tmpUserID') IS NOT NULL DROP TABLE dbo.#tmpUserID
SELECT User_ID 
INTO dbo.#tmpUserID
FROM users
WHERE Email_ID LIKE '[e-mail]'
  OR Email_ID LIKE '[e-mail]'
  OR Email_ID LIKE '[e-mail]'

-- 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 #tmpUserID
                                         )
                       )      
DELETE FROM orders 
WHERE User_ID NOT IN (
                      SELECT User_ID FROM #tmpUserID
                      )

-- delete albums not in test accounts
DELETE FROM albums 
WHERE User_ID NOT IN (
                      SELECT User_ID FROM #tmpUserID
                      )

- Aleksandr Fedorenko

Answer 3

这是一个非常好的问题。不幸的是，SQL没有提供很好的能力来组合SQL查询。

我能想到的最好的方法是使用视图或表值函数。您可以在脚本开头创建它，并在结尾处删除它。

虽然有点丑陋，但在这种情况下，它似乎仍然比动态构建SQL字符串要好。

- usr

Answer 4

如果您在表格上设置了外键，那么您可以将它们设置为级联删除。如果正确地完成，您只需要从用户表中删除即可，这将自动级联到所有其他表格。

关于级联删除有大量的文档资料可供参考，因此我在此不再赘述，但它绝对值得深入研究。

- George Mastros

---

我并不认为 OP 在这种情况下想要从 Users 表中删除，但级联删除可以节省一些删除操作。在上面的例子中，order_details 可以随着 album 一起被删除。 - Nenad Zivkovic

是的...这是一个遗留数据库，有几百个表格中有3个外键。显然，添加外键需要花费很长时间来清理数据库。所以，这是一个好主意，但无法执行 :( - thomasb

Answer 5

将结果存储在临时表中

   -- create temporary table to keep track of users
   CREATE TABLE #users
   (
      user_id BIGINT
   )

加载临时表

INSERT INTO #users (user_id)
SELECT User_ID FROM users
        WHERE Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'

然后你可以在其他查询中使用临时表。

-- 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
    )
)
DELETE FROM orders WHERE User_ID NOT IN (
    SELECT User_ID FROM #users
)

-- delete albums not in test accounts
DELETE FROM albums WHERE User_ID NOT IN (
    SELECT User_ID FROM #users
)

这种技术允许您遵循DRY原则，因为您只需在一个地方定义查询。如果您需要更改业务逻辑，您只需更改该查询，而不是像原始代码中那样更改3个查询。

- dcp

Answer 6

创建一个表变量或临时表，并存储Select查询数据。

 Declare @User table
 (User_Id int)

 Insert into @User
  SELECT User_ID FROM users
        WHERE Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]'
        OR Email_ID LIKE '[e-mail]' 

DELETE FROM order_details WHERE Album_ID NOT IN (
SELECT Album_ID FROM albums WHERE User_ID IN (
   Select User_Id from @User)
)

- praveen