表aae 每个项目编号下总价多少

表aac01 aac01 （项目编号） 1 2 表aae aae01 aae02 aae03 1 2 3 1 3 4 2 2 2 aac01 对应aae01 如何用一条语句查出 表aae 每个项目编号下总价多少 即 1 ---》2*3+3*4 2---> 2*2

 

create table aac01(aac01 int)
insert into aac01 values(1)
insert into aac01 values(2)
create table aae(aae01 int, aae02 int,aae03 int)
insert into aae values(1 ,2 ,3)
insert into aae values(1 ,3 ,4)
insert into aae values(2 ,2 ,2)
go

--考虑两表可能不匹配
select m.aac01 , 总价 = isnull((select sum(aae02*aae03) from aae n where n.aae01 = m.aac01),0) from aac01 m
/*
aac01       总价         
----------- -----------
1           18
2           4

（所影响的行数为 2 行）
*/

--不考虑两表匹配问题
select m.aac01 , 总价 = sum(aae02*aae03) from aac01 m , aae n where n.aae01 = m.aac01 group by m.aac01
/*
aac01       总价         
----------- -----------
1           18
2           4

（所影响的行数为 2 行）
*/

drop table aac01 , aae

 

 

IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TA
(
  COL1 INT
)
CREATE TABLE TB
(
  COL1 INT,
  COL2 INT,
  COL3 INT
)
INSERT INTO TA
SELECT  1 UNION ALL
SELECT  2
INSERT INTO TB
SELECT  1,2,3 UNION ALL
SELECT  1,3,4 UNION ALL
SELECT  2,2,2


SELECT TA.COL1,SUM(COL2*COL3) AS NUM
FROM TA JOIN TB ON TA.COL1=TB.COL1
GROUP BY TA.COL1

SELECT * FROM TA JOIN TB ON TA.COL1=TB.COL1

-----
COL1    NUM
1    18
2    4