sql 分组合计问题

[si12si12]

CREATE TABLE [dbo].[A](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TogetheCode] [varchar](50) NULL
)

CREATE TABLE [dbo].[B](
	[Code] [varchar](50) NOT NULL,
	[iNum] [float] NULL
)

INSERT INTO A(TogetheCode) VALUES ('P1/P3')
INSERT INTO A(TogetheCode) VALUES ('P2/P4')

INSERT INTO B(Code,iNum) VALUES('P1',4)
INSERT INTO B(Code,iNum) VALUES('P2',3)
INSERT INTO B(Code,iNum) VALUES('P3',2)
INSERT INTO B(Code,iNum) VALUES('P4',1)

想得到的查询结果如下

[卖水果的net]

select a.ID ,a.TogetheCode , b.Code , b.iNum , 
sum(iNum) over (partition by a.id) TogetheNum
from A , B where a.TogetheCode like '%' + b.Code + '%'
go


ID          TogetheCode Code       iNum                   TogetheNum
----------- ----------- ---------- ---------------------- ----------------------
1           P1/P3       P1         4                      6
1           P1/P3       P3         2                      6
2           P2/P4       P2         3                      4
2           P2/P4       P4         1                      4

(4 行受影响)