34,593
社区成员
发帖
与我相关
我的任务
分享
select B.projectName,SUM(A.Value+C.Value) as total
from
codeValue A
inner join consume B on A.code = B.fromCode
inner join codeValue C on B.toCode=C.code
group by B.projectName
projectName total
-------------------------------------------------- ---------------------------------------
石头方工程 32.00
土方工程 27.00
(2 行受影响)
create table codeValue
(
code varchar(10),
Value decimal(10,2)
)
create table consume
(
projectName varchar(50),
fromCode varchar(10),
toCode varchar(10)
)
insert into codeValue values ('1-1-1', '12')
insert into codeValue values ('1-1-3', '15')
insert into codeValue values ('1-2-3', '20')
insert into consume values ('土方工程', '1-1-1','1-1-3')
insert into consume values ('石头方工程', '1-1-1','1-2-3')
select t.projectName,SUM(t.Value) as 数量 from
(
select B.projectName,A.code,A.Value from codeValue A inner join consume B on A.code = B.fromCode
union
select B.projectName,A.code,A.Value from codeValue A inner join consume B on A.code = B.toCode
)t group by t.projectName
projectName 数量
-------------------------------------------------- ---------------------------------------
石头方工程 32.00
土方工程 27.00
(2 行受影响)