动态拼接的行转列SQL，如果为NULL，就会报错。

模拟场景：课程表里没有Java课程，我们查询Java课程的成绩，成功如下：

SET @sql = NULL;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c WHERE c.coursenm = 'java';
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno');
                        
SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

报错信息.png

原因分析

是由于课程表中没有Java这门课程，所以在拼接出的行转列SQL为NULL。因而导致出错。

SET @sql = NULL;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c WHERE c.coursenm = 'java';
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno');
                        
SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');

-- 由于没有找到MySQL中打印sql语句到控制台的方法，所以我采用将拼接好的SQL存到临时表中进行查看。
INSERT INTO reasons(sql_data) VALUES (@sql);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出拼接的SQL为NULL.png

正常情况输出的动态的SQL

SET @sql = NULL;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno');
                        
SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');

-- 由于没有找到MySQL中打印sql语句到控制台的方法，所以我采用将拼接好的SQL存到临时表中进行查看。
INSERT INTO reasons(sql_data) VALUES (@sql);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出正确SQL.png 格式化后的SQL.png

解决方法：

增加如下SQL

SET @sql =IFNULL(@sql,"SELECT NULL FROM DUAL");

完整SQL:

SET @sql = NULL;
 
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(c.coursenm = ''',
      c.coursenm,
      ''', s.scores, 0)) AS ''',
      c.coursenm, ''''
    )
  ) INTO @sql
FROM courses c;
 
SET @sql = CONCAT('SELECT st.stuid, st.stunm, ', @sql, 
                        ' From Student  st 
                        LEFT JOIN score s ON st.stuid = s.stuid
                        LEFT JOIN courses c ON c.courseno = s.courseno');
                        
SET @sql = CONCAT(@sql, ' GROUP BY st.stuid');

SET @sql =IFNULL(@sql,"SELECT NULL FROM DUAL");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;