数据补差问题
数据补差 怎么可以不写循环 用复合sql语句实现？请将下面cursor转换为复合sql语句
--补差
declare @zlyje decimal(12,2),@zhyje decimal(12,2),@lyje decimal(12,2),@hyje decimal(12,2),@ymclcode char(12),@ph varchar(60)
declare @ymcljglb char(8),@ymclflcode char(12)
declare hyje_cursor insensitive cursor for select distinct cljglb,clflcode from #cpcbhy_ymmxz_hz where cjdw=@cjdw and hsyear=2013 and hsmonth=7
open hyje_cursor
fetch next from hyje_cursor into @ymcljglb,@ymclflcode
while @@fetch_status=0
begin
select @zlyje=lyje from #cbsj where cljglb=@ymcljglb and clflcode=@ymclflcode
select @lyje=sum(lyje),@hyje=SUM(hyje) from #cpcbhy_ymmxz_hz where cjdw=@cjdw and hsyear=2013 and hsmonth=7 and cljglb=@ymcljglb and clflcode=@ymclflcode
select top 1 @ymclcode=clcode,@ph=ph from #cpcbhy_ymmxz_hz where cjdw=@cjdw and hsyear=2013 and hsmonth=7 and cljglb=@ymcljglb and clflcode=@ymclflcode
order by lysl desc
update #cpcbhy_ymmxz_hz set lyje=lyje +@zlyje - @lyje where cjdw=@cjdw and hsyear=2013 and hsmonth=7 and cljglb=@ymcljglb and clflcode=@ymclflcode and
clcode=@ymclcode and ph=@ph
fetch next from hyje_cursor into @ymcljglb,@ymclflcode
end
close hyje_cursor
deallocate hyje_cursor
[解决办法]
--create table #zsj(xb char(2),sl decimal(12,2) not null default 0)
-- insert into #zsj(xb,sl) values ('男',10)
-- insert into #zsj(xb,sl) values ('女',15)
-- create table #b1(xb char(2),rm char(12),sl decimal(12,2) not null default 0)
-- insert into #b1(xb,rm,sl)values('男','张三',3.3)
-- insert into #b1(xb,rm,sl)values('男','李四',3.3)
-- insert into #b1(xb,rm,sl)values('男','王五',3.3)
-- insert into #b1(xb,rm,sl)values('女','赵六',3.2)
-- insert into #b1(xb,rm,sl)values('女','钱七',5.7)
-- insert into #b1(xb,rm,sl)values('女','孙八',5.3)
-- declare @sl decimal(12,2),@zsl decimal(12,2),@rm char(12),@xb char(2)
-- declare hyje_cursor insensitive cursor for select xb,sl from #zsj
--open hyje_cursor
--fetch next from hyje_cursor into @xb,@zsl
--while @@fetch_status=0
--begin
--select @sl=sum(sl) from #b1 where xb=@xb
--select top 1 @rm=rm from #b1 where xb=@xb order by rm
--update #b1 set sl=sl +@zsl - @sl where xb=@xb and rm=@rm
--fetch next from hyje_cursor into @xb,@zsl
--end
--close hyje_cursor
--deallocate hyje_cursor
;WITH cte AS (
select a.*,b.sl AS totalsl,ROW_NUMBER()OVER(PARTITION BY a.xb ORDER BY a.sl DESC )id,(SELECT SUM(sl)sl FROM #b1 WHERE a.xb=#b1.xb GROUP BY xb)sumb1
from #b1 a INNER JOIN #zsj b ON a.xb=b.xb
)
,cte2 AS
(
SELECT xb,rm,sl,totalsl,id,CASE WHEN sumb1<totalsl THEN totalsl-sumb1 ELSE sl END sumb1
FROM cte
WHERE id=1
)
SELECT xb,rm,sl+sumb1 AS sl
FROM cte2
UNION ALL
SELECT *
FROM #b1 a
WHERE NOT EXISTS (SELECT 1 FROM cte2 b WHERE a.xb=b.xb
AND a.rm=b.rm)
/*
xb rm sl
---- ------------ ---------------------------------------
男 张三 3.40
女 钱七 6.50
男 李四 3.30
男 王五 3.30
女 赵六 3.20
女 孙八 5.30
*/
