--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 ?
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
a.ID,a.score,
sum(math) as math
from A
inner join B on a.id=b.pid
group by a.ID,a.score
能不能挑战一下,不使用Group By.
group by a.ID,a.score ,实际应用的时候字段很多。挺麻烦,连了好几张表。
a.ID,a.score,
isnull((select sum(math) FROM B WHERE pid=a.ID),0) as math
from A
当B表不存在对应记录时显示为0
a.ID,a.score,
isnull((select sum(math) FROM B WHERE pid=a.ID),0) as math
from A