22,199
社区成员
发帖
与我相关
我的任务
分享
songguozhi.esbu 16:51:37
DECLARE @tab TABLE(id INT, col1 INT, col2 INT, col3 INT)
INSERT INTO @tab(id, col1, col2, col3)
SELECT 0, 1, 2, 3 UNION ALL
SELECT 1, 2, 3, 4 UNION ALL
SELECT 2, 1, 1, 1 UNION ALL
SELECT 3, 3, 3, 3
SELECT * FROM @tab
/*
id col1 col2 col3
----------- ----------- ----------- -----------
0 1 2 3
1 2 3 4
2 1 1 1
3 3 3 3
*/
--现在合并其中id为的行
--也就是需要改进的办法,这种办法在多列时太不方便
UPDATE tab1 SET
col1 = tab1.col1 + tab2.col1,
col2 = tab1.col2 + tab2.col2,
col3 = tab1.col3 + tab2.col3
FROM @tab tab1
INNER JOIN @tab tab2
ON tab2.id = 0
WHERE tab1.id = 1
DELETE FROM @tab WHERE id = 0
SELECT * FROM @tab
/*
id col1 col2 col3
----------- ----------- ----------- -----------
1 3 5 7
2 1 1 1
3 3 3 3
*/
----------- ----------- ----------- -----------
1 3 5 7
2 1 1 1
3 3 3 3
(3 行受影响)
DECLARE @tab TABLE(id INT, col1 INT, col2 INT, col3 INT)
INSERT INTO @tab(id, col1, col2, col3)
SELECT 0, 1, 2, 3 UNION ALL
SELECT 1, 2, 3, 4 UNION ALL
SELECT 2, 1, 1, 1 UNION ALL
SELECT 3, 3, 3, 3
--SELECT * FROM @tab
select sum(id),sum(col1),sum(col2),sum(col3) from @tab where id in (0,1)
union all
select * from @tab where id not in (0,1)
=================
0,1 可以是条件
select id,col1,col2,col3 from tb where not 合并条件
union
select max(id),sum(col1),sum(col2),sum(col3) from tb where 合并条件
DECLARE @tab TABLE(id INT, col1 INT, col2 INT, col3 INT)
INSERT INTO @tab(id, col1, col2, col3)
SELECT 0, 1, 2, 3 UNION ALL
SELECT 1, 2, 3, 4 UNION ALL
SELECT 2, 1, 1, 1 UNION ALL
SELECT 3, 3, 3, 3
select sum(id),sum(col1),sum(col2),sum(col3) from @tab where id<2
union all
select * from @tab where id>=2
/*
----------- ----------- ----------- -----------
1 3 5 7
2 1 1 1
3 3 3 3
(3 行受影响)
*/