declare @var_now datetime
set @var_now=getdate()
declare @ee int
set @ee=(select datediff(dd,paydays,nextpaydays) from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1)
declare @ff int
set @ff=(select datediff(dd,paydays,@var_now) from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1 )
begin tran shiwu
if @ee> @ff
begin
select a.pkid,a.clientname,a.contractprice,a.qiantime,a.producttype,b.payid,b.paydays,b.nextpaydays,gou from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1
end
else
if @ee< @ff
begin
select a.pkid,a.clientname,a.contractprice,a.qiantime,a.producttype,b.payid,b.paydays,b.nextpaydays,cha from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1
end
commit tran shiwu
set @var_now=getdate()
declare @ee int
set @ee=(select datediff(dd,paydays,nextpaydays) from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1)
declare @ff int
set @ff=(select datediff(dd,paydays,@var_now) from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1 )
begin tran shiwu
if @ee> @ff
begin
select a.pkid,a.clientname,a.contractprice,a.qiantime,a.producttype,b.payid,b.paydays,b.nextpaydays,gou from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1
end
else
if @ee< @ff
begin
select a.pkid,a.clientname,a.contractprice,a.qiantime,a.producttype,b.payid,b.paydays,b.nextpaydays,cha from client as a left join clientpay as b on a.pkid=b.clientid and (b.paytoflag=1) where a.producttype=1
end
commit tran shiwu
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Server: Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
set @ff=(select datediff(dd,paydays,@var_now) from ....)
以上二个语句使用的子查询返回的结果多余一行.SQLSERVER要求子查询结果用于运算赋值时,必须保证子查询结果只有一行.
请楼主修改子查询的过滤条件,使查询结果唯一.
或者在子查询中
1.使用top 1限定只返回一行.
2.使用max(),min(),sum()等聚合函数.