创建视图时如何解析空值

chy5wohz  于 2021-06-17  发布在  Mysql
关注(0)|答案(3)|浏览(304)

我需要创建如下视图:

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

我有一张table studentMovement(id_studentmovement,id_student,month,year,cost,date,id_university,university_name) 这代表了学生在这个月和一年中的每一笔费用。它包含了一些学生在某所大学的某门课程中所做的实际成本。

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

查询输入了错误的值,有些值为空。有人能帮我吗?

8ftvxx2r

8ftvxx2r1#

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

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

ix0qys7i2#

Null 值显示在do中 Left Join 声明。因为它会返回 left table ,以及 right table . 结果是 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 JoinInner Join 而是因为 Inner Join 选择两个表中具有匹配值的记录。

jmo0nnb3

jmo0nnb33#

试着在模型中使用内部连接 from 条款:

FROM schema.students s JOIN
     schema.studentMovement sm 
     ON s.d_student = sm.id_student JOIN
     schema.university u
     ON u.id_university = sm.id_university

相关问题