sql - 使用一个连接查询多个表

Question

我需要针对特定​​条件查询包含列名称“idClient”的多个表。

到目前为止，我使用以下查询获取了需要查询的表:

SELECT c.name  AS 'ColumnName',
       t.name AS 'TableName'
FROM        sys.columns c (nolock)
JOIN        sys.tables  t   (nolock) ON c.object_id = t.object_id
WHERE       c.name LIKE '%idClient%'
ORDER BY    TableName,
            ColumnName;

这给了我以下结果(实际上返回了大约 100 个表):

+------------+-----------------+
| ColumnName |    TableName    |
+------------+-----------------+
| idClient   | tbClient        |
| idClient   | tbClientContact |
| idClient   | tbInvoice       |
+------------+-----------------+

为了让我在每个表中找到所有客户记录，我目前正在为每个表名运行 3 个单独的查询。例如:

SELECT * FROM tbClientContact (nolock)
    JOIN tbClient (nolock)
        ON tbClientContact.idClient = tbClient.idClient
WHERE tbClient.vcSurname = 'Smith'

除了对每个表运行上述查询 3 次之外，是否有更简单的方法对作为 TableName 返回的所有结果运行相同的查询？

目标:在上面的示例中，我的任务是从客户姓氏为“Smith”的数据库中删除所有客户记录。我正在运行上面的 SELECT 查询来查找所有姓氏为“Smith”的客户的 idClient 是否会在有“idClient”链接的表中留下孤立记录。我正在加入 tbClient，因为列 vcSurname 在除 tbClient 之外的任何其他表中都不存在。

Answer 1

你可以试试

SELECT idClient FROM tbClient WHERE vcSurname = 'Smith'
UNION ALL
SELECT idClient FROM tbClientContact WHERE vcSurname = 'Smith'
UNION ALL
SELECT idClient FROM tbInvoice  WHERE vcSurname = 'Smith'

如果你需要更多的列输出，所有三个查询必须有相同数量的输出列并且都是相同的类型

编辑正如其他人所建议的那样，了解您在做什么会很有帮助，因为您尝试的方法从来都不是做某事的最佳方法。但是，下面的游标解决方案应该构建一个动态查询来执行您想要的操作

DECLARE @table AS NVARCHAR(128)
DECLARE @sql AS NVARCHAR(4000)

DECLARE c CURSOR FOR
    -- hey all the tables with the columns LIKE '%idClient%'
    SELECT t.name AS 'TableName'
    FROM        sys.columns c (nolock)
    JOIN        sys.tables  t   (nolock) ON c.object_id = t.object_id
    WHERE       c.name LIKE '%idClient%'
    ORDER BY    TableName


-- loop through the results of the query line by line
OPEN c
FETCH NEXT FROM c INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    -- build the query dynamically
    SET @sql =CONCAT(@sql,'SELECT idClient FROM ' + @table +  ' WHERE vcSurname = ''Smith'' UNION ALL ')
    FETCH NEXT FROM c INTO @table
END

-- remove last "UNION ALL" text
SET @sql = STUFF(@sql,LEN(@sql)-9,11,'')
EXEC sp_executesql @stmt = @sql

CLOSE c
DEALLOCATE c

编辑编辑刚看到你的编辑。您的表是否有外键/主键对，外键是否有ON DELETE CASCADE？如果是这样，它应该只是一个例子

DELETE from tbClient WHERE vcSurname = 'Smith'