logo标签列表

SQL - 比较表中的行以查找列之间的差异 - 自连接

sqlsql-serversql-server-2012self-join
3

我有以下表格：

DECLARE @TABLE_A TABLE (
   id int identity, 
   name varchar(20), 
   start_date datetime, 
   end_date datetime, 
   details nvarchar(500), 
   copied_from int)

用户可以克隆一行并重新插入到同一张表中，我们记录从哪一行复制的。因此，如果您有一个ID = 1的行，并复制了它的所有列并重新插入（从UI），则会得到一个新的行，其ID = 5，新行的复制自字段将具有值为1。
在此之后，用户可以更新新行的值（例如此示例中的ID 5），我们需要一种方法来查看2行之间的差异。我编写了以下内容以获取ID 1和ID 5的列之间的差异。
DECLARE @id int = 5
DECLARE @TABLE_A TABLE (id int identity, name varchar(20), start_date datetime, end_date datetime, details nvarchar(500), copied_from int)

INSERT INTO @TABLE_A (name, start_date, end_date, details, copied_from)
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'John', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up - changed</p>', 1 

SELECT 
    'Name' AS column_name,
    ISNULL(s.name, '') AS value_before, 
    ISNULL(t.name, '') AS value_after, 
    t.id, 
    t.copied_from
FROM @TABLE_A s
FULL OUTER JOIN @TABLE_A t ON s.id = t.copied_from
WHERE t.id = @id AND ISNULL(s.name, '') <> ISNULL(t.name, '')
UNION ALL
SELECT 
    'Details' AS column_name,
    ISNULL(s.details, '') AS value_before, 
    ISNULL(t.details, '') AS value_after, 
    t.id, 
    t.copied_from
FROM @TABLE_A s
FULL OUTER JOIN @TABLE_A t ON s.id = t.copied_from
WHERE t.id = @id AND ISNULL(s.details, '') <> ISNULL(t.details, '')

.......

如您所见，ID和COPIED_FROM字段上有一个自连接，并且对于每个列，我都会检查是否存在差异。

这样做是有效的，但是我对每个列重复使用UNION操作不太满意，我想知道是否有其他方法可以实现这一点？

谢谢

这里有一个问题：你正在使用 UNION ALL，这意味着如果一个记录在两个查询中都被保留，它将会在结果集中出现两次。你知道这是否可能发生吗？ - Tim Biegeleisen
这很不可能，但感谢您指出。 - 03Usr
我的观点是，如果这种情况发生了，我不知道一个单一的查询如何能够替代它，因为一个单一的查询无法在结果集中产生相同的记录两次。 - Tim Biegeleisen
4个回答
1

尝试下面的脚本，这可能会对你有所帮助。使用 CASE WHEN 表达式，我们可以识别已修改的列。但是这将仅返回一个包含所有详细信息（之前的值、之后的值和状态-1:已修改/0:未修改）的单个记录。

SELECT  ISNULL(s.name, '')                                      AS name_before, 
        ISNULL(t.name, '')                                      AS name_after, 
        (case when s.name <> t.name then 1 else 0 end)          AS name_status,

        ISNULL(s.details, '')                                   AS details_before, 
        ISNULL(t.details, '')                                   AS details_after, 
        (case when s.details <> t.details then 1 else 0 end)    AS details_status
FROM    @TABLE_A s 
INNER JOIN @TABLE_A t ON s.id = t.copied_from
WHERE   t.id = @id
1
描述查看两行之间差异的逻辑实际上意味着“INNER JOIN”。即使没有这个“假设”，“WHERE”子句也会将此“FULL JOIN”转换为“RIGHT JOIN”。 - Ivan Starostin
1
你可以使用动态脚本，即使表格有数百列也没有关系。
   CREATE TABLE #tt (id int identity, name varchar(20), start_date datetime, end_date datetime, details nvarchar(500), copied_from int)

    INSERT INTO #tt (name, start_date, end_date, details, copied_from)
    SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
    SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
    SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
    SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
    SELECT 'John', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up - changed</p>', 1 
    DECLARE @cols VARCHAR(max),@sql VARCHAR(max)
    SELECT @cols=ISNULL(@cols+',('''+c.Name+''',CONVERT(VARCHAR,o.[','('''+c.Name+''',CONVERT(VARCHAR,o.[')+c.name+']),CONVERT(VARCHAR,c.['+c.name+']))'
    FROM tempdb.sys.columns AS c WHERE c.object_id=OBJECT_ID('tempdb..#tt')
    PRINT @cols
    SET @sql='
    SELECT x.* FROM #tt AS c
    LEFT JOIN #tt AS o ON c.copied_from=o.id
    CROSS APPLY(values'+@cols+') AS x(columnName,OrignalValue,CopiedValue) 
    WHERE c.copied_from IS NOT NULL'
    PRINT @sql
    EXEC(@sql)

列名          原始值                       复制后的值
----------- ------------------------------ ------------------------------
id          1                              5
name        Tom                            John
start_date  Jan  1 2017 12:00AM            Jan  1 2017 12:00AM
end_date    Feb  1 2017 12:00AM            Feb  1 2017 12:00AM
details     
此列可以包含HTML
此列可以包含HTML

copied_from NULL                           1
谢谢，这非常原创且对我很有帮助。 - 03Usr
1

我猜原始需求是将所有更改聚合到一个集合中，包括（列名、之前的值、之后的值、ID、复制自）。

我认为您可能希望使用unpivot，例如：

DECLARE @id int = 5
DECLARE @TABLE_A TABLE (id int identity, name varchar(20), start_date datetime, end_date datetime, details nvarchar(500), copied_from int)

INSERT INTO @TABLE_A (name, start_date, end_date, details, copied_from)
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'Tom', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up</p>', null UNION ALL
SELECT 'John', '2017-01-01', '2017-02-01', '<p>this column can contain html mark up - changed</p>', 1 ;

WITH    t AS ( SELECT   id,
                        CAST(ISNULL(name, '') AS NVARCHAR(500)) name,
                        CAST(start_date AS NVARCHAR(500)) start_date,
                        CAST(end_date AS NVARCHAR(500)) end_date,
                        details,
                        copied_from
               FROM     @TABLE_A
             ),
        m AS ( SELECT   u.id,
                        u.copied_from,
                        u.column_name,
                        u.data
               FROM     t UNPIVOT( data FOR column_name IN ( name, start_date,
                                                             end_date, details ) ) u
             )
    SELECT  toT.column_name,
            fromT.data value_before,
            toT.data value_after,
            toT.id,
            toT.copied_from
    FROM    m fromT
    INNER JOIN m toT ON toT.copied_from = fromT.id AND
                        toT.column_name = fromT.column_name AND
                        toT.data <> fromT.data;

注意：我必须将所有字段转换为 nvarchar（以保持需要 unpivot 的所有列的一致性），否则 UNPIVOT 将无法工作...
0

您可以使用INNER JOIN来实现这一点，但我认为在单行中显示更改值是正确的方式，也更易读。

    select s.name AS [name_before], 
    t.name AS [name_after], 
    s.details AS [detail_before], 
    t.details AS [detail_after], 
    t.id, 
    s.id AS [copied_from]
FROM @TABLE_A s 
INNER JOIN @TABLE_A t ON s.id = t.copied_from
WHERE   t.id = @id
    AND (s.name <> t.name OR s.details <> t.details)

如果我们需要显示名称和详细信息中的一个或两个发生了更改，我们可以使用OR。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接