mysql - 不使用sql选择不满足条件的id

Question

我有一个包含 RepId 和日期的表格。

Table: 1
RepID   logTime
108981  2013-04-09 00:00:00.000
108981  2013-04-09 00:00:00.000
108981  2013-04-11 00:00:00.000
108981  2013-04-11 00:00:00.000
108981  2013-04-11 00:00:00.000
108982  2013-04-09 00:00:00.000
108982  2013-04-10 00:00:00.000
108982  2013-04-11 00:00:00.000
108982  2013-04-11 00:00:00.000
108982  2013-04-11 00:00:00.000

如果 where 条件中指定的日期范围内至少有一个日期不匹配，我想对表 1 中的 RepId 感到遗憾。

我已经编写了这样的查询

select tl.repID from table1 tl where 
     CONVERT(date, logTime) > CONVERT(date,'2013-04-08 00:00:00.000') and CONVERT(date, logTime) < CONVERT(date,'2013-04-12 00:00:00.000')

这里的输出将类似于

RepID
108981
108982

但我想要它就像

RepID
108982

由于repId - 108981没有在where条件中指定日期“2013-04-10 00:00:00.000”。

----或-----如何取不满足的RepID和日期......

请帮助解决这个问题.........

Answer 1

尝试这样...

SELECT RepID FROM (Select RepID ,(Select Count(Distinct Cast(logTime as Date)) FROM T as A 
   WHERE 
   Cast( a.logTime as date) > Cast('2013-04-08 00:00:00.000' as date) 
   and Cast( a.logTime as date) < Cast('2013-04-12 00:00:00.000' as Date)
    AND
  A.RepID=b.RepID) AS COUNTS
   FROM T as B) as  c 
    WHERE COUNTS=datediff('2013-04-12 00:00:00.000','2013-04-08 00:00:00.000')-1 
    group by RepID

Sql Fiddle Demo