如何检查列表是否具有任何不同的值

Question

我有一个如下表：

(date1, date2, date3, date4, date5)

我想检查这些日期中的任何一个是否与其他日期不同。 平凡的解决方案是：

WHERE date1 <> date2
   OR date1 <> date3
   OR date1 <> date4
   OR date1 <> date5
   OR date2 <> date3
   OR date2 <> date4
   OR date2 <> date5
   OR date3 <> date4
   OR date3 <> date5
   OR date4 <> date5

有非平凡的解决方案吗？

Answer 1

顺便说一句，您的小案例实际上可以简化为仅

WHERE date1 <> date2
   OR date1 <> date3
   OR date1 <> date4
   OR date1 <> date5

使用德摩根定律，这与

WHERE NOT(date1 = date2
      AND date1 = date3
      AND date1 = date4
      AND date1 = date5)

相等关系，我们知道如果 date1 等于其他 4 个值，那么所有 5 个值都等于彼此。

Answer 2

如果表有主键，我想这不是小事。

select key, "There are duplicates"
from
(
    select key,date1 from table
    union all
    select key,date2 from table
    union all
    select key,date3 from table
    union all
    select key,date4 from table
    union all
    select key,date5 from table
) as aa
group by
  key, date1
having 
  count(*) > 1

Answer 3

按每个值分组，将按计数分组与原始计数进行比较

Answer 4

如果您使用的是 SQL Server 2005+，您可以执行以下操作：

With Dates As
    (
    Select PK, 'Date1' As DateType, Date1 As [Date] From Table
    Union All Select PK, 'Date2', Date2 From Table
    Union All Select PK, 'Date3', Date3 From Table
    Union All Select PK, 'Date4', Date4 From Table
    Union All Select PK, 'Date5', Date5 From Table
    )
Select D.PK, D.DateType, D.[Date]
From Dates As D
Where Exists    (
                Select 1
                From Dates As D1
                Where D1.PK = D.PK
                    And D1.[Date] <> D.[Date]
                )

Answer 5

对我来说，用一个例子更容易想象。这可行，但我不确定我是否过于复杂了。

CREATE TABLE #Dates( ID int, date1 datetime, date2 datetime, date3 datetime, date4 datetime )
INSERT INTO #Dates VALUES( 1, '1 Jan 2008', '2 Feb 2979', '8 Nov 1967', '31 Dec 2001' ) 
INSERT INTO #Dates VALUES( 2, '1 Jan 2008', '1 Jan 2008', '1 Jan 2008', '1 Jan 2008' ) 
INSERT INTO #Dates VALUES( 3, '1 Jan 2008', '1 Jan 2008', '1 Jan 2008', '31 Jan 2008' ) 
INSERT INTO #Dates VALUES( 4, '1 Jan 2008', '1 Jan 2008', '31 Jan 2008', '1 Jan 2008' ) 

-- look at example data - note only row 2 has all 4 dates the same
SELECT * FROM #Dates

-- return rows where the dates are not all the same 
SELECT ID as RowsWithDatesNotAllTheSame
FROM 
    (
    SELECT ID, Date
    FROM 
        (
        SELECT ID, DateCol, Date
        FROM 
            (SELECT ID, date1, date2, date3, date4 
            FROM #Dates) p 

        UNPIVOT
            ( Date FOR DateCol IN
                (date1, date2, date3, date4)
        ) AS unpvt
        ) x
    GROUP BY ID, Date
    ) y 
GROUP BY ID
HAVING count(*) > 1