ITEM_NO COL1 COL2
A
BITEM_NO COL1 COL2 COL3
A 100 100
A 100 100
B 100 100
B 100 100
B 100 100
-----------------------------------
要求结果如下
ITEM_NO COL1 COL2
A 200 200
B 300 300ITEM_NO COL1 COL2 COL3
A 100 100 50%
A 100 100 50%
B 100 100 33%
B 100 100 33%
B 100 100 33%
A
BITEM_NO COL1 COL2 COL3
A 100 100
A 100 100
B 100 100
B 100 100
B 100 100
-----------------------------------
要求结果如下
ITEM_NO COL1 COL2
A 200 200
B 300 300ITEM_NO COL1 COL2 COL3
A 100 100 50%
A 100 100 50%
B 100 100 33%
B 100 100 33%
B 100 100 33%
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta] ([ITEM_NO] [nvarchar](10),[COL1] [int],[COL2] [int])
INSERT INTO [ta]
SELECT 'A',NULL,NULL UNION ALL
SELECT 'B',NULL,NULL--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ITEM_NO] [nvarchar](10),[COL1] [int],[COL2] [int],[COL3] [nvarchar](10))
INSERT INTO [tb]
SELECT 'A','100','100',NULL UNION ALL
SELECT 'A','100','100',NULL UNION ALL
SELECT 'B','100','100',NULL UNION ALL
SELECT 'B','100','100',NULL UNION ALL
SELECT 'B','100','100',NULL
-->SQL查询如下:
UPDATE ta
SET COL1 = b.col1,col2=b.col2
FROM (
SELECT [ITEM_NO],SUM(col1) col1,SUM(col2) col2
FROM tb
GROUP BY ITEM_NO
) b
WHERE ta.ITEM_NO=b.ITEM_NOSELECT * FROM [ta]
/*
ITEM_NO COL1 COL2
---------- ----------- -----------
A 200 200
B 300 300(2 行受影响)
*/UPDATE tb
SET [COL3]=LTRIM(100*(tb.COL1+tb.col2)/(ta.col1+ta.col2))+'%'
FROM ta
WHERE tb.ITEM_NO=ta.ITEM_NOSELECT * FROM [tb]/*
ITEM_NO COL1 COL2 COL3
---------- ----------- ----------- ----------
A 100 100 50%
A 100 100 50%
B 100 100 33%
B 100 100 33%
B 100 100 33%(5 行受影响)
*/