又求一条SQL

再求一条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