Question

sql列求和问题

select col_code,(select sum(teacher_project_map.col_money) from teacher_project_map where teacher_project_map.col_code=base_teacher.col_code )as sum1,(select sum(teacher_reward_map.col_money) from teacher_reward_map where teacher_reward_map.col_code=base_teacher.col_code )as sum2,(select sum(teacher_paper_map.col_money) from teacher_paper_map where teacher_paper_map.col_code=base_teacher.col_code )as sum3,(select sum(col_money)from teacher_patent_map where teacher_patent_map.col_code=base_teacher.col_code)as sum4,(select sum(col_money) from teacher_standard_map where teacher_standard_map.col_code=base_teacher.col_code)as sum5,(select sum(col_money) from teacher_tachievement_map where teacher_tachievement_map.col_code=base_teacher.col_code)as sum6,(select sum1+sum2+sum3+sum4+sum5+sum6 )as sum7 from base_teacher where col_school = '信息中心' group by base_teacher.col_code
WITH ROLLUP

想要统计每列的和，我用的是WITH ROLLUP，为什么结果都是0？ 有什么其他方法么？跪求大神指点，感激不尽！！！

Answer 1

首先测试数据
select sum(col_money) from teacher_project_map where col_code='0000703' 查看数据是否与显示结果一致，如果显示的结果与图中的结果不一致代表语句确实有问题，可以按以下的句型结构测试数据是否正确
select
a.col_code
, sum(b.col_money) as sum1
, sum(c.col_money) as sum2
, sum(d.col_money) as sum3
, sum(e.col_money) as sum4
, sum(f.col_money) as sum5
, sum(g.col_money) as sum6
from
(
select col_code
from base_teacher
where col_school = '信息中心'
group by col_code
) a
left join teacher_project_map b on a.col_code=b.col_code
left join teacher_reward_map c on a.col_code=c.col_code
left join teacher_paper_map d on a.col_code=d.col_code
left join teacher_patent_map e on a.col_code=e.col_code
left join teacher_standard_map f on a.col_code=f.col_code
left join teacher_tachievement_map g on a.col_code=g.col_code

追问

我的数据经测试是好的，我想要的是在现在查询的基础上 求出每列的和

追答

那可能是因为那些sum列都是计算列，在group by 作用的表中并不存在指定的列，因此rollup统计不到，用上面关联表句型，加group by a.col_code with rollup 试试

追问

多表的left join 会产生笛卡尔积问题，之前我已经用过这种方法。不过还是非常感谢您