检查每组的所有行是否有两列相等

Assuming a DB like this:

假设有这样的DB:

Date | Attribute1 | Attribute2 | ... | AttributeN
-------------------------------------------------
1    | A          | C          | ... | ...
1    | B          | C          | ... | ...
2    | A          | A          | ... | ...
2    | B          | B          | ... | ...
2    | A          | A          | ... | ...
3    | B          | B          | ... | ...
3    | A          | A          | ... | ...
4    | B          | C          | ... | ...
4    | A          | A          | ... | ...

I am trying to find for which unique dates (they are actual dates in the real case but I don't think that matters), ALL elements of Attribute1 are equal to their corresponding elements in Attribute2. The result for the example data above would be

我正在尝试找出哪一个唯一的日期(它们在实际情况中是实际的日期，但我认为这并不重要)，Attribute1的所有元素都等于它们在Attribute2中的相应元素。上面示例数据的结果是

Date
----
2
3

Because for each record that has date equal to 2 (and the same for 3) , Attribute1 is equal to Attribute2. 4 is not returned because although the last record in the sample does meet the criterion (since A equals A), the second last record does not (since B does not equal C).

因为对于每个日期为2的记录(对于3也是一样)，Attribute1等于Attribute2。4不返回，因为尽管示例中的最后一个记录满足条件(因为A等于A)，但是第二个最后的记录不满足条件(因为B不等于C)。

I could not work out how to write this query, I was hoping for some aggregate function (shown as ALL(...) in the code below) that would allow me to write something like:

我不知道如何编写这个查询，我希望有一个聚合函数(如下面的代码中显示为ALL(…))，可以让我编写如下内容:

SELECT Date
FROM myTable
GROUP BY Date
HAVING ALL(Attribute1 = Attribute2)

Is there such a function? Otherwise is there a clever way to this using COUNT maybe?

有这样一个函数吗?否则有什么聪明的方法可以使用COUNT ?

3 个解决方案

#1

You can use HAVING and CASE:

你可以用have和CASE:

SELECT [Date]
FROM #tab
GROUP BY [Date]
HAVING SUM(CASE WHEN Attribute1 = Attribute2 THEN 0 ELSE 1 END) = 0

LiveDemo

LiveDemo

Otherwise is there a clever way to this using COUNT maybe?

否则有什么聪明的方法可以使用COUNT ?

Why not :) Version with COUNT:

为什么不:)版本数:

SELECT [Date]
FROM #tab
GROUP BY [Date]
HAVING COUNT(CASE WHEN Attribute1 <> Attribute2 THEN 1 END) = 0

LiveDemo2

LiveDemo2

#2

Perhaps this:

也许是这样的:

select Date from Test
Except
Select Date from Test where Attribute1 <> Attribute2

#3

The logic is easier if you turn it around. You want all unique DATE values where there isn't a row where Attribute1 is different to Attribute2:

如果你把它转过来，逻辑会更简单。你想要所有唯一的日期值当Attribute1与Attribute2不同的行时:

SELECT DISTINCT [Date] FROM myTable
WHERE [Date] NOT IN (
   SELECT [Date] FROM myTable
   WHERE Attribute1 != Attribute2)

#1