求一个更新的SQL语句请大师指教！

lsj3073 2012-02-28 11:02:28

数据如下：
STAT_MONTH HOMEAREA COUNTYNAME GWAP GJAVA LYJ PHONEZF WOLX
201111 格尔木茫崖 4600 4000 2400 0 0
201111 海西州茫崖 15800 0 6200 2000 0

需求如下
如果是海西州的茫崖放到格尔木的茫崖，当然海西州的芒崖下的这些费用 GWAP GJAVA LYJ PHONEZF WOLX
应该更新到格尔木下面去。

意思是这个地区有交叉需要给整合一下，最终要把海西州茫崖剔除掉。不知我描述的是否够清楚请指教

...全文

221 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

lsj3073 2012-02-28

打赏
举报

STAT_MONTH HOMEAREA COUNTYNAME GWAP GJAVA LYJ PHONEZF WOLX
201111 格尔木茫崖 4600 4000 2400 0 0
201111 海西州茫崖 15800 0 6200 2000 0

zhangcong615 2012-02-28

打赏
举报

----------------------1。将海西州茫崖后面的数据加到格尔木茫崖列
update 数据表 set(GWAP, GJAVA, LYJ ,PHONEZF ,WOLX)= (
select a.Gwap+b.Gwap ,a.GJAVA+b.GJAVA ,a.LYJ+b.LYJ,a.PHONEZF+b.PHONEZF ,a.WOLX+b.WOLX from (
select stat_month ,GWAP, GJAVA, LYJ,PHONEZF ,WOLX from 数据表 where homearea='格尔木' and countyname='茫崖'
) a join
(
select stat_month ,GWAP, GJAVA, LYJ ,PHONEZF ,WOLX from 数据表 where homearea='海西州' and countyname='茫崖'
) b on a.stat_month=b.stat_month )
where HOMEAREA='格尔木' and COUNTYNAME='茫崖';
---------------2.删除行海西州茫崖
delete from 数据表 where homearea='海西州' and countyname='茫崖';
commit;

lsj3073 2012-02-28

打赏
举报

跟我想的一样，我是这样做的但想知道有其他别的方法不

Update SP_HQBL_RISHOU_2G
Set Gwap = (Select sum(Gwap) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Gjava = (Select sum(Gjava) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Lyj = (Select sum(Lyj) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Phonezf = (Select sum(Phonezf) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Wolx =(Select sum(Wolx) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Sms = (Select sum(Sms) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Duanxin = (Select sum(Duanxin) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Mms = (Select sum(Mms) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖'),
Xl = (Select sum(Xl) From SP_HQBL_RISHOU_2G where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖')
Where Stat_Month = v_n_stat_cycle
And Countyname = '茫崖';
commit；

----删除海西州芒崖

Delete From SP_HQBL_RISHOU_2G where homearea='海西州' And countyname='茫崖' And stat_month=v_n_stat_cycle;
Commit;

xpingping 2012-02-28

打赏
举报

update tb set GWAP= GWAP+(select GWAP from tb where HOMEAREA ='海西州' and COUNTYNAME='茫崖' ),

GJAVA= GJAVA+(select GJAVA from tb where HOMEAREA ='海西州' and COUNTYNAME='茫崖' ),

LYJ =LYJ +(select LYJ from tb where HOMEAREA ='海西州' and COUNTYNAME='茫崖' ),

PHONEZF =PHONEZF +(select PHONEZF from tb where HOMEAREA ='海西州' and COUNTYNAME='茫崖' ),

WOLX =WOLX +(select WOLX from tb where HOMEAREA ='海西州' and COUNTYNAME='茫崖' )

where HOMEAREA ='格尔木' and COUNTYNAME='茫崖' 

然后

delete from tb where HOMEAREA ='海西州' and COUNTYNAME='茫崖'

lsj3073 2012-02-28

打赏
举报

[Quote=引用 7 楼 benluobobo 的回复:]
为什么是把海西州的芒崖数值加到格尔木的芒崖上
而不是反过来
[/Quote]

因为需要把海西州的芒崖数值加到格尔木的芒崖上加之海西州下面不应该有芒崖。

lsj3073 2012-02-28

打赏
举报

因为需要把海西州的芒崖数值加到格尔木的芒崖上加之海西州下面不应该有芒崖。

benluobo 2012-02-28

打赏
举报

为什么是把海西州的芒崖数值加到格尔木的芒崖上
而不是反过来

lsj3073 2012-02-28

打赏
举报

[Quote=引用 2 楼 bin3509 的回复:]
楼主是想把海西州茫崖去掉。然后把海西州茫崖的数值加到格尔木茫崖上面去吗
[/Quote]

是的就是这样。

不是只单纯的把HOMEAREA 的海西州改成格尔木那样很好弄。

因为海西州的芒崖和格尔木的芒崖交叉了所以需要去掉海西州把海西州的芒崖数值加到格尔木的芒崖上