SQL Server——比较同一表中不同行的多个列

I have a table that holds the values read from 2 meters:

我有一个表，它包含从2米开始的值:

----------------------------
   Date   | MeterID | Value
----------------------------
  1/2/14       A       1.3
  2/2/14       A       1.8
  2/2/14       B       3.8
  3/3/14       A       1.2
  4/3/14       A       1.8
  4/3/14       B       2.9

I need a query that will count the number of days that a reading exists for BOTH meter types (A & B)?
In the example above this should yield 2 as the result.

我需要一个查询，它将计算两种仪表类型(a & B)的读数的天数。在上面的示例中，结果应该是2。

Thanks.

谢谢。

2 个解决方案

#1

You can use a temporary table to list [Date]s when there were occurrences in MeterID for both A and B and then COUNT() all this [Date]s :

您可以使用临时表列出a和B的MeterID中出现的日期，然后计数()所有这些[日期]s:

SELECT COUNT(t.*) 
FROM ( SELECT [Date] 
       FROM [table] 
       GROUP BY [Date] 
       HAVING COUNT(DISTINCT [MeterID]) = 2
     ) t

#2

Another solution, using sets:

另一个解决方案,使用集:

select count(*) from
(
    select distinct date from table where meterid='A'
    intersect
    select distinct date from table where meterid='B'
) x

#1