--DROP TABLE A
CREATE TABLE A
(
id int primary key identity(1,1),
score decimal(18,2)
)
--DROP TABLE B
CREATE TABLE B
(
id int primary key identity(1,1),
pid int , --A表的ID
math decimal(18,2),
english decimal(18,2)
)
SELECT * FROM A
INSERT INTO A
VALUES(50)
SELECT * FROM B
INSERT INTO B
VALUES(1,5,5)
INSERT INTO B
VALUES(1,5,5)
INSERT INTO B
VALUES(1,15,15)
--想获得子表中math总分
想获得SQL语句取得以下结果
id score math
----------- ---------------------------------------
1 50.00 ?
select #a.id,#a.score,SUM(#b.math) as math
from #A,#B
where #A.id = #B.pid
group by #A.id,#A.score
select A.ID,A.Score,SUM(math)as math from A inner Join B on A.id=B.pid group by A.id,A.score
select
a.ID,a.score,
sum(math) as math
from A
inner join B on a.id=b.pid
group by a.ID,a.score
select
a.ID,a.score,
isnull((select sum(math) FROM B WHERE pid=a.ID),0) as math
from A
select
a.ID,a.score,
isnull((select sum(math) FROM B WHERE pid=a.ID),0) as math
from A
