数据库为SQL Server
endtime字段为可空,语法没有错误,但就是查询的结果和预期不一样,请大家帮忙看看, select * from orders where id ='5072' and (endtime is null or (coalesce(endtime,'2050-01-01') > '2009-12-01' and coalesce(endtime,'2050-01-01') > passtime ) )
得到的结果是:没有行
预期的结果是:找出endtime为空的、endtime大于当前日期并且endtime大于passtime的
在线等,谢谢
endtime字段为可空,语法没有错误,但就是查询的结果和预期不一样,请大家帮忙看看, select * from orders where id ='5072' and (endtime is null or (coalesce(endtime,'2050-01-01') > '2009-12-01' and coalesce(endtime,'2050-01-01') > passtime ) )
得到的结果是:没有行
预期的结果是:找出endtime为空的、endtime大于当前日期并且endtime大于passtime的
在线等,谢谢
-- Author: T.O.P
-- Create date: 2009/12/01
-- Version: SQL SERVER 2005
-- =============================================
declare @tb table([id] int,[endtime] datetime,[passtime] datetime)
insert @tb
select 5072,null,'2010-03-11' union all
select 5072,'2009-12-01','2010-01-25' union all
select 5072,'2010-10-07','2010-02-15' union all
select 5072,'2009-03-07','2010-02-15'select *
from @tb
where [endtime] is null or
([endtime]>getdate()) and [endtime]>[passtime]
--测试结果:
/*
id endtime passtime
----------- ----------------------- -----------------------
5072 NULL 2010-03-11 00:00:00.000
5072 2010-10-07 00:00:00.000 2010-02-15 00:00:00.000(2 row(s) affected)
*/
declare @tb table([id] int,[endtime] datetime,[passtime] datetime)
insert @tb
select 5072,null,'2010-03-11' union all
select 5072,'2009-12-01','2010-01-25' union all
select 5072,'2010-10-07','2010-02-15' union all
select 5072,'2009-03-07','2010-02-15'
select *
from @tb
where
id ='5072' and
(coalesce(endtime,'2050-01-01') > '2009-12-01' and coalesce(endtime,'2050-01-01') > passtime ) --测试结果:
/*
id endtime passtime
----------- ----------------------- -----------------------
5072 NULL 2010-03-11 00:00:00.000
5072 2010-10-07 00:00:00.000 2010-02-15 00:00:00.000(2 row(s) affected)
*/
为什么"(endtime is null or (coalesce(endtime,'2050-01-01') > '2009-12-01' and coalesce(endtime,'2050-01-01') > passtime ) ) "
要在一个括号内,去掉最外层那个括号试试.
改成:
endtime is null or (coalesce(endtime,'2050-01-01') > '2009-12-01' and coalesce(endtime,'2050-01-01') > passtime )
coalesce(endtime,'2050-01-01') > '2009-12-01' and coalesce(endtime,'2050-01-01') > passtime 这里逻辑有问题,如果endtime非空,那么
coalesce(endtime,'2050-01-01')==endtime
1,endtime为空时的结果集 或者
2,endtime非空时,endtime > '2009-12-01'并且endtime > pasttime的结果集
datediff(day , pasttime, endtime) > 0