select *
  from  
              (select Cwno,DeptName,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
               from GkjlL_His_His a
               inner join
               OPENDATASOURCE('SQLOLEDB','Data Source=172.17.240.93;User ID=sa;Password=!QAZ2wsx').pddbgz.dbo.[YwCk] e
               on  a.Cwno = e.Wno) a
               where a.BatchCnt !='0' and  DATEDIFF(S,Ctime,Etime)<5
               GROUP BY DeptName,a.Cwno,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
               having Etime like '__:__:__'  

--异常数据举例
select *
  from  
              (select Cwno,DeptName,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
               from GkjlL_His_His a
               inner join
               OPENDATASOURCE('SQLOLEDB','Data Source=172.17.240.93;User ID=sa;Password=!QAZ2wsx').pddbgz.dbo.[YwCk] e
               on  a.Cwno = e.Wno) a
               where a.BatchCnt !='0' and  DATEDIFF(S,Ctime,Etime)<5
               GROUP BY DeptName,a.Cwno,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
               having Etime is not null


以上两个语句为什么执行的结果不一样？求大神帮忙啊啊

肯定呢,
当你查询的是一个varchar或者nvarchar类型时,如果这个类型允许为空的话:\
必须要用:is not null 或者is null
在数据库中:null跟' '是有很大区别的

'__:__:__'与null等同？

肯定呢,
当你查询的是一个varchar或者nvarchar类型时,如果这个类型允许为空的话:\
必须要用:is not null 或者is null
在数据库中:null跟' '是有很大区别的


回复：是允许为空的。
select *
  from  
              (select Cwno,DeptName,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
               from GkjlL_His_His a
               inner join
               OPENDATASOURCE('SQLOLEDB','Data Source=172.17.240.93;User ID=sa;Password=!QAZ2wsx').pddbgz.dbo.[YwCk] e
               on  a.Cwno = e.Wno) a
               where a.BatchCnt !='0' and  DATEDIFF(S,Ctime,Etime)<5
               GROUP BY DeptName,a.Cwno,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
              （ having Etime is not null）

可是我用不用最后一句，查询出来的结果都是一样的！用了这句（ having Etime is not null），Etime字段的空值还是会显示喔！

having Etime like '__:__:__'  就是要求输出的Etime的值非空吖！在这里应该是和is not null等价的吧？

可是我用了这句（ having Etime is not null），Etime字段的空值还是会输出喔！为什么呢？

你要排除Etime的空值，为什么不在where 之后加入 and Etime is not null

having Etime like '__:__:__'  就是要求输出的Etime的值非空吖
   是要求输出Etime字段只有字符'__:__:__'的数据

select *
  from  
              (select Cwno,DeptName,Cdate ,Cseno,a.BatchCnt,Ctime,Etime
               from GkjlL_His_His a
               inner join
               OPENDATASOURCE('SQLOLEDB','Data Source=172.17.240.93;User ID=sa;Password=!QAZ2wsx').pddbgz.dbo.[YwCk] e
               on  a.Cwno = e.Wno) a
               where a.BatchCnt !='0' and  DATEDIFF(S,Ctime,Etime)<5 and Etime is not null
               GROUP BY DeptName,a.Cwno,Cdate ,Cseno,a.BatchCnt,Ctime,Etime

这样还是会输出空值。。只有用 Etime like '__:__:__'   才能排除。可能是数据库设计的原因。。