数据补差 怎么可以不写循环 用复合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
--补差
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
insert into #zsj(sl) values (10)
create table #b1(rm char(12),sl decimal(12,2) not null default 0)
insert into #b1(rm,sl)values('张三',3.3)
insert into #b1(rm,sl)values('李四',3.3)
insert into #b1(rm,sl)values('王五',3.3)
declare @sl decimal(12,2),@zsl decimal(12,2),@rm char(12)
declare hyje_cursor insensitive cursor for select sl from #zsj
open hyje_cursor
fetch next from hyje_cursor into @zsl
while @@fetch_status=0
begin
select @sl=sum(sl) from #b1
select top 1 @rm=rm from #b1 order by rm
update #b1 set sl=sl +@zsl - @sl where rm=@rm
fetch next from hyje_cursor into @zsl
end
close hyje_cursor
deallocate hyje_cursorselect * from #b1
我就是想把中间的cursor 替换成复合sql语句
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_cursorselect * from #b1
你看这个脚本把
-- 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
*/
-- 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)
ORDER BY xb,rm
/*
xb rm sl
---- ------------ ---------------------------------------
男 李四 3.30
男 王五 3.30
男 张三 3.40
女 钱七 6.50
女 孙八 5.30
女 赵六 3.20
*/
步骤2:
update #b1
set #b1.sl=#t.sl
from #b1 inner join #t on #b1.xb=#t.xb and #b1.rm=#t.rm
SELECT xb,rm,sl+sumb1 AS sl into #b6 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) order by xb,rm
这样能insert into 能不能直接update