不用游标,批量将#B中的coin列的值加到#A表的coin中(注:保留重复值)
以下是表结构和参考数据
CREATE TABLE #A(id INT, coin INT)INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)CREATE TABLE #B(id INT, coin INT)INSERT INTO #B VALUES(1,2)
INSERT INTO #B VALUES(2,2)
INSERT INTO #B VALUES(3,2)
INSERT INTO #B VALUES(3,2)
INSERT INTO #B VALUES(1,2)
INSERT INTO #B VALUES(1,2)
以下是表结构和参考数据
CREATE TABLE #A(id INT, coin INT)INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(1,10)CREATE TABLE #B(id INT, coin INT)INSERT INTO #B VALUES(1,2)
INSERT INTO #B VALUES(2,2)
INSERT INTO #B VALUES(3,2)
INSERT INTO #B VALUES(3,2)
INSERT INTO #B VALUES(1,2)
INSERT INTO #B VALUES(1,2)
insert into #b(id,coin) select id,coin from #a
INSERT INTO #A VALUES(1,10)
INSERT INTO #A VALUES(2,10)
INSERT INTO #A VALUES(3,10)
INSERT INTO #A VALUES(4,10)
INSERT INTO #A VALUES(5,10)
INSERT INTO #A VALUES(6,10)
INSERT INTO #A VALUES(7,10)
1 16
2 12
3 14
4 10
5 10
6 10
7 10
我是想得到两个表中的列coin值的和
#A中结果应该是这样的:
id coin
1 16
2 12
3 14
4 10
5 10
6 10
7 10
select id,sum(coin) coin
(select * from #a
unoin
select * from #b )
group by id
set coin=a.coin+b.coin
from #a a,(
select id,sum(coin) as coin
from #b
group by id
) as b
where a.id=b.id