mysql - 如何为select语句设置默认值

Question

我有这个问题，但默认值显示为空。

SELECT c.course_id, 
       cast(AVG(fb.course_rate) as decimal(2,1)), 
       cast(AVG(fb.difficult) as decimal(2,1)),
       course_code,
       date, 
       course_col, 
       course_level, 
       course_name 
FROM course AS c 
LEFT JOIN course_feedback AS fb
  ON fb.course_id = c.course_id 
Where course_code LIKE '%$course_code%'  
GROUP BY c.course_id" 

我需要为列设置默认值0

cast(AVG(fb.course_rate) as decimal(2,1))

Answer 1

您似乎在 fb.course_rate 列中有 NULL 值。您可以使用 COALESCE()函数来处理它们

SELECT c.course_id, 
       cast(COALESCE(AVG(fb.course_rate), 0) as decimal(2,1)), 
       cast(AVG(fb.difficult) as decimal(2,1)),
       course_code,
       date, 
       course_col, 
       course_level, 
       course_name 
FROM course AS c 
LEFT JOIN course_feedback AS fb
  ON fb.course_id = c.course_id 
Where course_code LIKE '%$course_code%'  
GROUP BY c.course_id"