当前位置: 代码迷 >> Sql Server >> 又求一条SQL
  详细解决方案

又求一条SQL

热度:35   发布时间:2016-04-25 01:20:31.0
再求一条SQL
现在有2张表 结构完全相同:
数据也完全相同。
OPEN_NUM CLOSE_NUM M_OPEN_NUM M_CLOSE_NUM MANAGER BIT_M
1 1 0 0 aaaaa 1
3 1 0 0 aaaaa 0
0 1 0 0 aaaaa 0
2 2 0 0 bbbbb 1
1 0 0 0 bbbbb 0
3 3 0 0 bbbbb 0
5 5 0 0 ccccc 1


将MANAGER group by 一下
4 3 0 0 aaaaa 1
6 5 0 0 bbbbb 1
5 5 0 0 ccccc 1

现在要更新到 CLOSE_NUM M_OPEN_NUM 2列里面 而且BIT_M 要等于1
如下:
OPEN_NUM CLOSE_NUM M_OPEN_NUM M_CLOSE_NUM MANAGER BIT_M
1 1 4 3 aaaaa 1
3 1 0 0 aaaaa 0
0 1 0 0 aaaaa 0
2 2 6 5 bbbbb 1
1 0 0 0 bbbbb 0
3 3 0 0 bbbbb 0
5 5 5 5 ccccc 1

求大神罩!

------解决方案--------------------
我懂了,用这句,你看看你的问题里面,更新的列都说错了。
SQL code
WITH cte AS ( SELECT SUM(OPEN_NUM)OPEN_NUM, SUM(CLOSE_NUM )CLOSE_NUM,SUM(M_OPEN_NUM)M_OPEN_NUM, SUM(M_CLOSE_NUM)M_CLOSE_NUM ,MANAGER, SUM(BIT_M)BIT_M FROM #tb GROUP BY MANAGER) SELECT * FROM cte   UPDATE a SET a.M_OPEN_NUM=b.OPEN_NUM, a.M_CLOSE_NUM=b.CLOSE_NUM FROM #tb a INNER JOIN cte b ON a.MANAGER=b.MANAGER WHERE a.BIT_M=1
  相关解决方案