请教简单UPDATE语句.

[PBVC]

ITEM_NO COL1 COL2
A
B

ITEM_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 300

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%

[htl258_Tony]

--> 生成测试数据表: [ta]
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_NO

SELECT * 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_NO

SELECT * 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 行受影响)
*/

[jaydom]

[Quote=引用 1 楼 htl258 的回复:]
SQL code
--> 生成测试数据表: [ta]
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 U……
[/Quote]
TONY哥的，顶起