mysql - 创建 View 时如何解决空值

Question

我需要创建一个像这样的 View :

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,月,年,成本,日期,id_university,university_name)

代表学生本月和当年的每一笔费用。它包含了一些学生在某所大学完成某门类(class)的实际成本。

Table Students(id_student,name)

Table University(id_university,university name);

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

这个sql查询是这样的:

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

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

Answer 1

Null 值出现在 do Left Join 语句中。因为它返回左表中的所有记录，以及右表中匹配的记录。如果没有匹配，结果从右侧开始为 NULL。

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 
    inner 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), 
   s.id_student, 
   s.name,  
   u.id_university,
   u.university_name

如果您不想看到 Null，请将 Left Join 更改为 Inner Join，因为 Inner Join 选择两个表中都有匹配值的记录。