[30分]求助基础问题，在线等待；环境创建的代码和测试数据已经给出
现有2基础数据表，数据如下
TBL1 数据
school class1 class2 class3
-------- ----------- ----------- -----------
内 11.00 11.00 11.00
内 12.00 12.00 12.00
外 13.00 13.00 13.00
外 14.00 14.00 14.00
TBL2 数据
school hope1 hope2 hope3
------- ----------- ----------- -----------
内 110.00 110.00 110.00
内 120.00 120.00 120.00
外 130.00 130.00 130.00
外 140.00 140.00 140.00
期望结果：
school class1SUM class2SUM class3SUM hope1SUM hope2SUM2 hope3SUM
------ -------- -------- --------- -------- -------- --------
内 23.00 23.00 23.00 230.00 230.00 230.00
外 27.00 27.00 27.00 270.00 270.00 270.00
请问：
SELECT tbl1.school ,sum(class1) as class1 ,sum(class2) as class2 ,sum(class3) as class3 ,
sum(hope1) as hope1 ,sum(hope2) as hope2 ,sum(hope3) as hope3
from tbl1 left join tbl2 on tbl1.school = tbl2.school
group by tbl1.school
school class1SUM class2SUM class3SUM hope1SUM hope2SUM2 hope3SUM
------ -------- -------- --------- -------- -------- --------
内 46.00 46.00 46.00 460.00 460.00 460.00
外 54.00 54.00 54.00 540.00 540.00 540.00
为什么达不到期望结果，且和期望数据有出入？请给出原理解释和实现方法；谢谢
[解决办法]
SELECT tbl1.school ,sum(class1) as class1 ,sum(class2) as class2 ,sum(class3) as class3 ,
sum(hope1) as hope1 ,sum(hope2) as hope2 ,sum(hope3) as hope3
from tbl1 inner join tbl2 on tbl1.school = tbl2.school
group by tbl1.school
[解决办法]
关注！
[解决办法]
去掉group by
SELECT tbl1.school ,sum(class1) as class1 ,sum(class2) as class2 ,sum(class3) as class3 ,
sum(hope1) as hope1 ,sum(hope2) as hope2 ,sum(hope3) as hope3
from tbl1 left join tbl2 on tbl1.school = tbl2.school
看一下结果就会明白了。
[解决办法]
create table tbl1
(school varchar(10),class1 int,class2 int,class3 int)
insert into tbl1 select '内 ',11,11,11
union all select '内 ',12,12,12
union all select '外 ',13,13,13
union all select '外 ',14,14,14
create table tbl2
(school varchar(10),hope1 int,hope2 int,hope3 int)
insert into tbl2 select '内 ',110,110,110
union all select '内 ',120,120,120
union all select '外 ',130,130,130
union all select '外 ',140,140,140
go
select ta.school,[class1SUM],[class2SUM],[class3SUM],[hope1SUM],[hope2SUM],[hope3SUM]
from
(select school,[class1SUM]=sum(class1),[class2SUM]=sum(class2),[class3SUM]=sum(class3) from tbl1 group by school)ta
left join
(select school,[hope1SUM]=sum(hope1),[hope2SUM]=sum(hope2),[hope3SUM]=sum(hope3)from tbl2 group by school)tb
on ta.school=tb.school
school class1SUM class2SUM class3SUM hope1SUM hope2SUM hope3SUM
---------- ----------- ----------- ----------- ----------- ----------- -----------
内 23 23 23 230 230 230
外 27 27 27 270 270 270
（所影响的行数为 2 行）
