创建视图时如何解决空值 [英] How resolve null value when create view

问题描述

我需要创建一个这样的视图:

I need to create a view like this:

STUDENT JANUARY FEBRUARY MARCH ........ DECEMBER
miki     10.23   23.23    0               0
Goku     10        0       0               0
 Luffy    0       0        0               0

我有一张表 studentMovement(id_studentmovement,id_student,month,year,cost,date,id_university,university_name)

代表学生在本月和本年的每一笔费用.它包含了某些学生在某所大学的某门课程中完成的真实成本.

that represents every sigle cost for the student in this month and year. it contains the real cost that some students had done in a certain course in a certain university.

Table Students(id_student,name)

Table University(id_university,university name);

我想为所有大学、所有学生和单个学生获得每个月的费用(如果学生没有在某所大学花费任何费用:

I want to get for all university, all student and for single student the cost for every months ( also if the student doesn't spent anything in a certain university:

这个sql查询是这样的:

This sql query is this:

select year(sm.date) as year, s.id_student, s.name,
       sum(amount) as year_amount,
       sum(case when month(sm.date) = 1 then amount else 0 end) as january,
       sum(case when month(sm.date) = 2 then amount else 0 end) as february,
       . . .
       sum(case when month(sm.date) = 12 then amount else 0 end) as december,


       u.id_university as id_university,
       u.university_name as university_name
from ((schema.students s left join
     schema.studentMovement sm 
     on s.d_student = sm.id_student ) inner join schema.university u on u.id_university=sm.id_university) 

group by year(sm.date) as year, s.id_student, s.name,  u.id_university,
       u.university_name

查询输入了错误的值，有些值为空.有人可以帮助我吗?

THe query put wrong value and some value are null.Anyone can help me?

推荐答案

也许您注意到金额字段中的一些错误值:尝试删除双引号

Maybe you noticed some wrong values in the amount field: try to delete the double quotes

COALESCE(SUM(sm.amount), 0) AS amount,

这篇关于创建视图时如何解决空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！