select
case
when ContractEndTime=''
then
(select ContractName from dbo.ContractExecution where ContractBeginTime <'2013-10-1 ')
else
(select ContractName from ContractExecution where ContractBeginTime <'2013-10-1 ' and ContractEndTime>'2012-10-1' )
end
from dbo.ContractExecution
--where
order by ID desc
--消息 512,级别 16,状态 1,第 1 行
-子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
case
when ContractEndTime=''
then
(select ContractName from dbo.ContractExecution where ContractBeginTime <'2013-10-1 ')
else
(select ContractName from ContractExecution where ContractBeginTime <'2013-10-1 ' and ContractEndTime>'2012-10-1' )
end
from dbo.ContractExecution
--where
order by ID desc
--消息 512,级别 16,状态 1,第 1 行
-子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
和
(select ContractName from ContractExecution where ContractBeginTime <'2013-10-1 ' and ContractEndTime>'2012-10-1' )
的结果集 都只能有一条记录。
FROM dbo.ContractExecution
WHERE ContractBeginTime < CASE WHEN ContractEndTime = '' THEN '2013-10-1 '
ELSE '2013-10-1 '
END
AND ContractEndTime > CASE WHEN ContractEndTime = '' THEN 0
ELSE '2012-10-1'
END
ORDER BY ID DESC
select ContractName from dbo.ContractExecution where ContractBeginTime <'2013-10-1 '
else
select ContractName from ContractExecution where ContractBeginTime <'2013-10-1 ' and ContractEndTime>'2012-10-1'
以上两种方法都可以实现楼主想要的。
您的方法,第二个不可行
casewhen的用法还有里面的几种,有心情就去看一下吧。对编程有用