主页 / dba / 问题 / 16711
Accepted
Ben Brocka
Asked: 2012-04-19 10:25:01 +0800 CST

识别与主行不匹配的行

  • 772

我正在将来自不同服务器上不同数据库的一堆表与主记录进行比较。我需要知道由 标识的哪些服务器locationID具有不匹配的行，因为它们可能需要维护。

我有一个简单的EXCEPT查询，我比较了一个表，其中每一行都是来自每个服务器的配置；table1每台服务器有一行，所有配置加上locationID这是一个告诉我它是哪台服务器的列。我将所有这些与table1_master具有正确设置的表进行比较，但我排除了它，locationID因为它不匹配。

下面的简单查询：

SELECT everything, but, locationID
FROM table1
EXCEPT
SELECT everything, but, locationID
FROM table1_master

我比较所有服务器时只有一个主行，我没有locationID在这里选择它。

这是我正在比较的行的一个示例。每个都有一个主键、一个列varchar和一个包含数十个列的巨大列表。我想比较除LocationID 之外的所有列，但我需要 LocationID 来识别行。

LocationID             setting    setting    setting     setting
CS02      C            Y           Y         Y           Y
CS03      C            Y           Y         Y           Y
CS06      C            Y           N         Y           Y

在此示例中，CS02 是我的主记录，因此由于 CS02 和 CS03 中的所有设置都相同，因此这些行不会显示，但 CS06 会显示。但在我的EXCEPT查询中，我实际上并没有捕获 LocationID，所以我实际上并不知道返回了哪一行。

这会返回我需要的行，但不是locationID，所以我不知道哪些行是错误的。有什么方法可以locationID在踢出匹配行的同时包含在结果集中？

我想到的解决方案是为table1_master表中的每个服务器创建一行，因此每个都locationID被表示，但除此之外它们都具有相同的数据。然后我的EXCLUDE查询应该返回locationID和我的信息，但这是最好的方法吗？

sql-server sql-server-2005

3 个回答

  1. Best Answer

    您也可以使用动态 SQL 来执行此操作，而无需手动构建所有列名。

    DECLARE @sql NVARCHAR(MAX), @c1 NVARCHAR(MAX), @c2 NVARCHAR(MAX);

SELECT @c1 = N'', @c2 = N'';

SELECT 
  @c1 = @c1 + ',' + QUOTENAME(name),
  @c2 = @c2 + ' AND m.' + QUOTENAME(name) + ' = s.' + QUOTENAME(name)
 FROM sys.columns
 WHERE name <> 'LocationID'
 AND [object_id] = OBJECT_ID('dbo.table1');

SET @sql = ';WITH s AS (
       SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1
       EXCEPT 
       SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1_master
     ) 
     SELECT m.LocationID
 FROM s INNER JOIN dbo.table1 AS m ON 1 = 1
 ' + @c2;

SELECT @sql;
--EXEC sp_executesql @sql;

    您可以按原样获取此查询的输出并将查询存储在某处，或者您可以注释掉SELECT并取消注释EXEC并将其保留为永久动态 SQL - 在这种情况下，它将自动适应两个表中的列更改。

    另一个想法（假设 LocationID 是唯一的） - 我突然想到您可能想要包含主行，以便您可以快速发现不同的列：

      ;WITH c AS 
  (
    SELECT t.LocationID, m.setting1, m.setting2, ...
      FROM dbo.table1 AS t CROSS JOIN dbo.table1_master AS m
  )
  SELECT DISTINCT src = '> master', setting1, setting2, ...
    FROM c
  UNION ALL
  (
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM dbo.table1
    EXCEPT
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM c
  )
  ORDER BY src;

    这个版本稍微便宜一些（主要是通过避免DISTINCT与主表对抗，代价是需要再次指定所有列 - 您可以再次按照上面的方式自动化）：

      ;WITH m AS 
  (
    SELECT setting1, setting2, ... 
      FROM dbo.table1_master
  ),
  c AS 
  (
    SELECT src = RTRIM(t.LocationID), m.setting1, m.setting2, ...
      FROM dbo.table1 AS t CROSS JOIN m
  )
  SELECT src = '> master', setting1, setting2, ...
    FROM m
  UNION ALL
  (
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM dbo.table1
    EXCEPT
    SELECT src, setting1, setting2, ...
      FROM c
  )
  ORDER BY src;

    然而，所有这些选项的性能都比 Rachel 的 simple 更差，而且计划更差LEFT JOIN。我试图坚持使用的主题，EXCEPT即使它更多地是关于语法而不是性能。

    关键要点是，如果列数太多而无法手动处理，您可以使用上面的动态 SQL 方法来构造您想要使用的任何查询 - 您可以执行一次并存储结果，或者拥有代码每次生成。要使用动态 SQL 生成 Rachel 的查询，不需要太多更改：

    DECLARE @sql NVARCHAR(MAX), @and NVARCHAR(MAX), @anycol NVARCHAR(128);
SELECT @sql = N'', @and = N'';

SELECT @and = @and + ' AND t.' + QUOTENAME(name) + ' = m.' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('dbo.table1_master');

SELECT TOP (1) @anycol = QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('dbo.table1_master')
  ORDER BY name;

SET @sql = 'SELECT locationID
FROM dbo.table1 AS t
LEFT OUTER JOIN dbo.table1_master AS m ON 1 = 1' 
  + @and + ' WHERE m.' + @anycol + ' IS NULL;';

SELECT @sql;
--EXEC sp_executesql @sql;
    • 4

  2. 我会推荐：

    • 创建一个Hash作为持久计算列的字段，其定义如下：HASHBYTES('SHA1', Field1 + Field2 + Field3...)
    • 仅HASH将“主”中的值与其他记录进行比较
    • 显示来自不匹配行的所有实际值

    就像是

    SELECT *
FROM Table1
WHERE HashField <> (SELECT Hashfield FROM Table1_Master)
    • 1

  3. 仅在每列上加入两个表（或使用 where 语句）并选择第二个表中不存在的项目有什么问题？

    SELECT locationID
FROM table1
LEFT OUTER JOIN table1_master 
    ON table1.a = table1_master.a
    AND table1.b = table1_master.b
    AND table1.c = table1_master.c
WHERE table1_master.a is null

    它可能不漂亮，但它应该工作

    • 1

相关问题