如何选择日期相同的两个键值对的值

Question

因此，如果日期相同，我只想显示两个键值对。因此，例如，如果一个键值对与另一个键值配对的日期时间不完全相同，那么根本不要显示它们。。。

所以我的逻辑如下，但我不知道如何实现它：

SELECT ForeignKeyID, Value, Date
  FROM tblMyTable
//declare myDate= null ????
  where (ForeignKeyID = 1 and Value > 14) or (ForeignKeyID = 2 and Value > 10)
//somehow check if myDate from the first key-value pair has the exact same myDate as the second one or vice versa.

以下是我想要和不想要显示的内容：

Time                    ForeignKeyID Value
2015-05-27 00:30:00.000     1       15.000 //Yes because same date for 2 exists
2015-05-27 00:30:00.000     2       13.800 //Yes because same date for 1 exists
2015-05-27 00:45:00.000     1       15.000 //Yes
2015-05-27 00:45:00.000     2       13.800 //Yes
2015-05-27 01:00:00.000     1       15.000 //Yes
2015-05-27 01:00:00.000     2       13.300 //Yes
2015-05-27 01:15:00.000     1       15.000 //Yes
2015-05-27 01:15:00.000     2       13.300 //Yes
2015-05-27 01:30:00.000     2       13.300 //No because the same date for 1 doesnt exist!
2015-05-27 01:45:00.000     2       13.300 //No because the same date for 1 doesnt exist!
2015-05-27 02:00:00.000     1       15.000 //No because the same date for 2 doesnt exist!
2015-05-27 02:15:00.000     1       15.000 //No because the same date for 2 doesnt exist!

真的很抱歉我在这里的无知，但我正在努力学习。。。

Answer 1

在这种情况下，自联接可能是最简单的：

select t1.ForeignKeyID, t1.Value, t1.Date,
       t2.ForeignKeyID, t2.Value, t2.Date
from tblMyTable t1 join
     tblMyTable t2
     on t1.ForeignKeyID = 1 and t1.value > 14 and
        t2.ForeignKeyID = 2 and t2.value > 10 and
        t1.date = t2.date;

这确实将匹配的值放在一行而不是两行中。

编辑：

如果想要两行，可以使用复杂的exists逻辑：

select t.ForeignKeyID, t.Value, t.Date
from tblMyTable t
where (t.ForeignKeyID = 1 and t.value > 14 and
       exists (select 1
               from tblMyTable t2
               where t2.ForeignKeyID = 2 and t2.value > 10 and t2.date = t.date
              )
      ) or
      (t.ForeignKeyID = 2 and t.value > 10 and
       exists (select 1
               from tblMyTable t2
               where t2.ForeignKeyID = 1 and t2.value > 14 and t2.date = t.date
              )
      );

我认为第一个版本可能更有用。

或者，一种更简单的方法是使用窗口函数：

select t.ForeignKeyID, t.Value, t.Date
from (select t.*,
             sum(case when ForeignKeyID = 1 and value > 14 then 1 else 0 end) over (partition by date) as cnt1,
             sum(case when ForeignKeyID = 2 and value > 10 then 1 else 0 end) over (partition by date) as cnt2
      from mytable t
      where (ForeignKeyID = 1 and Value > 14) or (ForeignKeyID = 2 and Value > 10)
     ) t
where cnt1 > 0 and cnt2 > 0

Answer 2

您可以使用分析函数COUNT OVER来查看每个日期获得的密钥数：

select foreignkeyid, value, date
from
(
  select 
    foreignkeyid, 
    value, 
    date, 
    count(distinct foreignkeyid) over (partition by date) as cnt
  from tblmytable
  where (foreignkeyid = 1 and value > 14) or (foreignkeyid = 2 and value > 10)
) counted
where cnt = 2
order by date, foreignkeyid;