mysql - 错误代码 : 1064. 您的 SQL 语法有错误；检查与您的 MySQL 服务器对应的手册

Question

以下存储过程向我抛出 SQL 错误。我不知道是什么原因造成的。

    DROP TABLE IF EXISTS tmp_results_dailyearnings;

CREATE TEMPORARY TABLE tmp_results_dailyearnings AS
        SELECT emp_id, date_, sum(ifnull(reg_hours,0)) reg_hours, sum(ifnull(ot_hours,0)) ot_hours, SUM(ifnull(allowance,0)) allowance,
                (sum(ifnull(reg_hours,0)) + sum(ifnull(ot_hours,0)) + SUM(ifnull(allowance,0))) gross, SUM(ifnull(LEAV,0)) SIL
        FROM vw_totaldailyearnings
        WHERE date_ between "2018-05-08" and "2018-05-21"
        GROUP BY emp_id, date_
        ORDER BY date_ ASC;

            SELECT CONCAT('
            SELECT emp_id, concat(b.last_name,'','',b.first_name,'','',b.middle_name) name, ',cores_by_dates,', IFNULL(ot_hours,0) ot_hours, IFNULL(allowance,0) allowance,
                    gross, SIL
            FROM tmp_results_dailyearnings a
            inner join tbl_employee b
            on a.emp_id = b.id
            GROUP BY a.emp_id, a.ot_hours, a.allowance, a.gross
            ORDER BY a.emp_id'
            )
            INTO @query
            FROM
            (
            SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN date_=''',actual_date,''' THEN reg_hours END), ''0'') AS "',col_name,'"')) cores_by_dates
            FROM (
            SELECT actual_date, DATE_FORMAT(actual_date,'%a %m/%d') AS col_name
            FROM (SELECT DISTINCT date_ AS actual_date FROM tmp_results_dailyearnings) AS dates
            ) dates_with_col_names
            ) result;

            drop table if exists tmp_earningsReport;
            SET @createSQL = CONCAT('CREATE TEMPORARY TABLE tmp_earningsReport AS ', @query);
            PREPARE statement FROM @createSQL;
            EXECUTE statement;
            DEALLOCATE PREPARE statement;

我不断收到的错误消息是检查与您的 MySQL 服务器版本相对应的手册，以获取在 'ifnull(ot_hours,0) ot_hours, IFNULL(allowance,0) grant,gross 附近使用的正确语法, 0 SIL' 第 2 行

请帮忙。谢谢!

Answer 1

错误的引号序列..在字符串选择代码中，您仅使用单引号，但如果您需要包装sql代码，则应该使用双引号以避免与内部单引号发生冲突

 SELECT CONCAT("
        SELECT emp_id, concat(b.last_name,'','',b.first_name,'','',b.middle_name) name, ',cores_by_dates,', IFNULL(ot_hours,0) ot_hours, IFNULL(allowance,0) allowance,
                gross, SIL
        FROM tmp_results_dailyearnings a
        inner join tbl_employee b
        on a.emp_id = b.id
        GROUP BY a.emp_id, a.ot_hours, a.allowance, a.gross
        ORDER BY a.emp_id'" 
        )