检查两行之间是否存在差异

I have the following table:

我有下表:

column_a column_b column_c column_d column_date
1        test_1   test_1   type_1   11:00
2        test_2   test_2   type_2   11:01
3        test_3   test_6   type_2   11:02
4        test_4   test_4   type_3   11:03
5        test_2   test_6   type_2   11:04
6        test_1   test_2   type_1   11:05

I have to check which row has another value in column_b and column_c filtered by the column_d (type). I must analyze with my sql script only the first two rows ordered by column_date for each type. If there is a difference between the two rows in one of the columns (column_b, column_c or column_d), I have to print out the value of the first row (otherwise null).

我必须检查哪一行在column_b中有另一个值,column_c由column_d(type)过滤。我必须用我的sql脚本分析每个类型的column_date排序的前两行。如果其中一列(column_b,column_c或column_d)中的两行之间存在差异,则必须打印出第一行的值(否则为null)。

In example above I expect the following result:

在上面的例子中,我期望得到以下结果:

column_a column_b column_c column_d column_date
5        test_2   null     type_2   11:04
6        null     test_2   type_1   11:05

I can use T-SQL of the MS SQL Server 2008.

我可以使用MS SQL Server 2008的T-SQL。

1 个解决方案

#1

It is not clear for me what datatype you use for column_date column, but let us assume that it is time one.

我不清楚你为column_date列使用什么数据类型,但让我们假设它是时间一。

 DECLARE @Table TABLE (
        column_a    INT          ,
        column_b    VARCHAR (128),
        column_c    VARCHAR (128),
        column_d    VARCHAR (128),
        column_date [time]         );

    INSERT  INTO @Table
    VALUES 
    ('1', 'test_1', 'test_1', 'type_1', '11:00'),
    ('2', 'test_2', 'test_2', 'type_2', '11:01'),
    ('3', 'test_3', 'test_6', 'type_2', '11:02'),
    ('4', 'test_4', 'test_4', 'type_3', '11:03'),
    ('5', 'test_2', 'test_6', 'type_2', '11:04'),
    ('6', 'test_1', 'test_2', 'type_1', '11:05');

    WITH     C
    AS       (SELECT *,
                     ROW_NUMBER() OVER (PARTITION BY column_d ORDER BY column_date DESC) AS RN
              FROM   @Table),
             ToCompare
    AS       (SELECT *
              FROM   c
              WHERE  Rn < 3
                     AND EXISTS (SELECT *
                                 FROM   c AS C2
                                 WHERE  C.column_d = C2.column_d
                                        AND Rn = 2))
    SELECT   T.column_a,
             NULLIF (T.column_b, T2.column_b) AS [column_b],
             NULLIF (T.column_c, T2.column_c) AS [column_c],
             T.column_d,
             T.column_date
    FROM     ToCompare AS T
             INNER JOIN
             ToCompare AS T2
             ON T.column_d = T2.column_d
    WHERE    T.Rn = 1
             AND T2.RN = 2
    ORDER BY T.column_a;