declare AA CURSOR FOR
SELECT DISTINCT GP_CZ,GP_DBPF,gp_gypf,GP_ZTC,GP_ZBP FROM NEWHL
open aa
fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP --从NEWHL中取出没重复的这几个列
while @@fetch_status=0
begin
declare bb cursor for select GP_SG from newhl
WHERE GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---在aa结果集中可能有多个gp_sg值 ,把gp_sg的值取出来给变量@ljmx
SET @LJMX='与'
OPEN BB
FETCH NEXT FROM BB INTO @SG
WHILE @@FETCH_STATUS=0
BEGIN
SET @LJMX=RTRIM(@LJMX)+RTRIM(@SG)
END
FETCH NEXT FROM BB INTO @SG
SET @LJMX=RTRIM(@LJMX) +'同穿'
---@ljmx实际等于 GP_sg列中多个值相加(一定要读出所有符合BB条件的值)
close bb
deallocate bb
---------------------------------------------------------------------------
declare bb cursor for select gp_sg,gp_zuo from newhl
where GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---这下面是根据newhl表中gp_sg和gp_zuo 的值更改gp_zlj1,gp_zlj2等列的值
open bb
fetch next from bb into @sg, @zuo
while @@fetch_status =0
begin
if len(rtrim(@sg)) =3
begin
if @zuo='Z'
begin
update newhl set gp_zlj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
update newhl set gp_ylj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
begin
if @zuo='Z'
update newhl set gp_zlj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
else
update newhl set gp_ylj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
end
fetch next from bb into @sg, @zuo
close bb
deallocate bb
------------------------------------------------------------------------------
endfetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBPclose aa
deallocate aa
end
GO
SELECT DISTINCT GP_CZ,GP_DBPF,gp_gypf,GP_ZTC,GP_ZBP FROM NEWHL
open aa
fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP --从NEWHL中取出没重复的这几个列
while @@fetch_status=0
begin
declare bb cursor for select GP_SG from newhl
WHERE GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---在aa结果集中可能有多个gp_sg值 ,把gp_sg的值取出来给变量@ljmx
SET @LJMX='与'
OPEN BB
FETCH NEXT FROM BB INTO @SG
WHILE @@FETCH_STATUS=0
BEGIN
SET @LJMX=RTRIM(@LJMX)+RTRIM(@SG)
END
FETCH NEXT FROM BB INTO @SG
SET @LJMX=RTRIM(@LJMX) +'同穿'
---@ljmx实际等于 GP_sg列中多个值相加(一定要读出所有符合BB条件的值)
close bb
deallocate bb
---------------------------------------------------------------------------
declare bb cursor for select gp_sg,gp_zuo from newhl
where GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---这下面是根据newhl表中gp_sg和gp_zuo 的值更改gp_zlj1,gp_zlj2等列的值
open bb
fetch next from bb into @sg, @zuo
while @@fetch_status =0
begin
if len(rtrim(@sg)) =3
begin
if @zuo='Z'
begin
update newhl set gp_zlj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
update newhl set gp_ylj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
begin
if @zuo='Z'
update newhl set gp_zlj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
else
update newhl set gp_ylj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
end
fetch next from bb into @sg, @zuo
close bb
deallocate bb
------------------------------------------------------------------------------
endfetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBPclose aa
deallocate aa
end
GO
解决方案 »
- 执行 Transact-SQL 语句或批处理时发生了异常。
- VB连接sql有密码时连接出错!
- 通过外键约束的表设置删除规则为“层叠”后删除速度非常慢是怎么回事
- 视图如何能快速转成一个表
- sql 出现些错误
- 求一段SQL规律, 或者代码. 求各位老大们帮帮忙...
- 郁闷了好几天了,请各位大虾帮帮忙!!!
- 一个高级SQL查询问题
- 我想做数据库同步,可我的sql server注册是local,我现在想把它改成计算机名,改不掉!怎么办呢?
- 請問倉庫管理系統怎樣設計?
- 在企业管理器中,修改表中记录出现以下错误如何处理?"错误信息:不能在firehose 方式下启动事务"
- 我想把数据库中两张表的数据汇总后相减,写入第三张表中,这样的SQL语句怎么写啊?
134 234 B8-97371-981-6Z AB GHIJ @ SOX-S5X0.3(B) 25 Z
134 234 B8-97371-981-6Z CDEF @ SOX-S5X0.3(B) 25 Z
135 234 B8-97371-981-6Z AB GHIJ @ SOX-S5X0.3(B) 25 Z
135 234 B8-97371-981-6Z CDEF @ SOX-S5X0.3(B) 25 Z
242 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
243 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
244 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
246 234 B8-97371-981-6Z 全品番 シ ES7.44X50(GR) Y
247 234 B8-97371-981-6Z 全品番 シ ES7.44X50(GR) Y
248 234 B8-97371-981-6Z 全品番 シ ES7.44X50(GR) Y
250 234 B8-97371-981-6Z 全品番 サ ES7.44X50(GR) Y
251 234 B8-97371-981-6Z 全品番 サ ES7.44X50(GR) Y
252 234 B8-97371-981-6Z 全品番 サ ES7.44X50(GR) Y
254-A 234 B8-97371-981-6Z 全品番 コ ES7.44X50(GR) Z
255-A 234 B8-97371-981-6Z 全品番 コ ES7.44X50(GR) Z
256-A 234 B8-97371-981-6Z 全品番 コ ES7.44X50(GR) Z
258 234 B8-97371-981-6Z 全品番 ク ES7.44X50(GR) Z
260 234 B8-97371-981-6Z 全品番 ク ES7.44X50(GR) Z
412-A 234 B8-97371-981-6Z CDEFGHIJ セ ES5.72X50(GR) Z
413-A 234 B8-97371-981-6Z CDEFGHIJ セ ES5.72X50(GR) Z
479 234 B8-97371-981-6Z AB GHIJ @ ES7.44X50(GR) Y
这个是测试数据
现在的目的就是要把gp_cz GP_DBPF GP_GYPF GP_ZTC GP_ZBP这几列数据完全相同
做个更改
如果gp_zuo的值='z'and len(gp_sg)=3 更改gp_zlj1
如果gp_zuo的值='z'and len(gp_sg)=5 更改gp_zlj2
如果gp_zuo的值='y'and len(gp_sg)=3 更改gp_ylj1
如果gp_zuo的值='y'and len(gp_sg)=3 更改gp_ylj2
比如:gp_sg gp_cz gp_dbpf gp_gypf gp_ztc gp_zbp gp_zuo
242 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
243 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
244 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
我要的结果是:gp_sg gp_cz gp_dbpf gp_gypf gp_ztc gp_zbp gp_zuo
242 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
243 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y
244 234 B8-97371-981-6Z 全品番 ス ES7.44X50(GR) Y gp_zlj1 gp_zlj2 gp_ylj1 gp_ylj2 这几列存更该内容
242 与243,244同穿
243 与242,244同穿
244 与242,243同穿
如果gp_zuo的值='y'and len(gp_sg)=5 更改gp_ylj2 这里应该是5
SELECT DISTINCT GP_CZ,GP_DBPF,gp_gypf,GP_ZTC,GP_ZBP FROM NEWHL
open aa
fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP --从NEWHL中取出没重复的这几个列
while @@fetch_status=0
begin
declare bb cursor for select GP_SG from newhl
WHERE GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---在aa结果集中可能有多个gp_sg值 ,把gp_sg的值取出来给变量@ljmx
SET @LJMX='与'
OPEN BB
FETCH NEXT FROM BB INTO @SG
WHILE @@FETCH_STATUS=0
BEGIN
SET @LJMX=RTRIM(@LJMX)+RTRIM(@SG)
END
FETCH NEXT FROM BB INTO @SG
SET @LJMX=RTRIM(@LJMX) +'同穿'
---@ljmx实际等于 GP_sg列中多个值相加(一定要读出所有符合BB条件的值)
close bb
deallocate bb
---------------------------------------------------------------------------
declare bb cursor for select gp_sg,gp_zuo from newhl
where GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---这下面是根据newhl表中gp_sg和gp_zuo 的值更改gp_zlj1,gp_zlj2等列的值
open bb
fetch next from bb into @sg, @zuo
while @@fetch_status =0
begin
if len(rtrim(@sg)) =3
begin
if @zuo='Z'
begin
update newhl set gp_zlj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
update newhl set gp_ylj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
begin
if @zuo='Z'
update newhl set gp_zlj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
else
update newhl set gp_ylj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
end
fetch next from bb into @sg, @zuo
close bb
deallocate bb
------------------------------------------------------------------------------
endfetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBPclose aa
deallocate aa
end
GO
SELECT DISTINCT GP_CZ,GP_DBPF,gp_gypf,GP_ZTC,GP_ZBP FROM NEWHL
open aa
fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP --从NEWHL中取出没重复的这几个列
while @@fetch_status=0
begin
declare bb cursor for select GP_SG from newhl
WHERE GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---在aa结果集中可能有多个gp_sg值 ,把gp_sg的值取出来给变量@ljmx
SET @LJMX='与'
OPEN BB
FETCH NEXT FROM BB INTO @SG
WHILE @@FETCH_STATUS=0
BEGIN
SET @LJMX=RTRIM(@LJMX)+RTRIM(@SG)
--END
FETCH NEXT FROM BB INTO @SG
end ----这里加end,否则没循环 ------
SET @LJMX=RTRIM(@LJMX) +'同穿'
---@ljmx实际等于 GP_sg列中多个值相加(一定要读出所有符合BB条件的值)
close bb
deallocate bb
---------------------------------------------------------------------------
declare bb cursor for select gp_sg,gp_zuo from newhl
where GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
---这下面是根据newhl表中gp_sg和gp_zuo 的值更改gp_zlj1,gp_zlj2等列的值
open bb
fetch next from bb into @sg, @zuo
while @@fetch_status =0
begin
if len(rtrim(@sg)) =3
begin
if @zuo='Z'
begin
update newhl set gp_zlj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
update newhl set gp_ylj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
begin
if @zuo='Z'
update newhl set gp_zlj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
else
update newhl set gp_ylj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
--end
fetch next from bb into @sg, @zuo
end ----这里加end,否则没循环
close bb
deallocate bb
------------------------------------------------------------------------------
--end ----这个不要fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP
end ----这里加end,否则没循环
close aa
deallocate aa
end
GO