mysql - 数据透视表错误

Question

我有一个 SQL 查询来显示有关公司的信息。其中一列称为 PieceType，公司可以有许多件类型，并且每种类型都有一个数量。所以我想将每种件类型显示为一列，然后显示其下的数量。因此每种类型都有自己的列。

所以它看起来像这样:

我在这里创建了一个示例数据库:http://www.sqlfiddle.com/#!9/13230/4

SET group_concat_max_len=4294967294;
SET @COLUMNS = NULL;

/* Build columns to pivot */

SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'CASE WHEN jp.PieceType = "', 
            jp.PieceType ,
            '" THEN 1 ELSE NULL END AS ',
            jp.PieceType
        )
) INTO @COLUMNS
FROM job_pieces jp;

/* Build full query */
SET @SQL = CONCAT(
        'SELECT    
                c.Name,
                ',@COLUMNS'
        FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.ID'
);

/* Prepare and execute the query*/
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但是当我运行查询时出现此错误:

Incorrect parameters in the call to native function 'CONCAT'

Answer 1

您的代码存在一些问题，导致了一些问题。

首先，设置@SQL的代码缺少CONCAT函数的逗号`:

SET @SQL = CONCAT(
        'SELECT    
                c.Name,
                ',@COLUMNS, '
                       -- ^ this is missing  
        FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.Name'
);

添加逗号后，您将收到另一个错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE,CASE WHEN jp.PieceType = "CTN" THEN 1 ELSE NULL END AS CTN,CASE WHEN jp.Pie' at line 3

此错误是由于您获取列列表的代码所致。由于您有一个名为 CASE 的 jp.PieceType 值，它是一个保留字，因此您的语法失败。您需要转义列名的别名:

SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'CASE WHEN jp.PieceType = ''', 
            jp.PieceType ,
            ''' THEN 1 ELSE NULL END AS `',
                                     -- ^ add this
            jp.PieceType, '`'
                     -- ^ add this after the comma
        )
) INTO @COLUMNS
FROM job_pieces jp;

这是一个sql fiddle代码现在可以工作了。但是，我还必须注意，您缺少 CASE 表达式周围的聚合函数。通常，当您对这样的数据进行 PIVOT 时，您会使用聚合函数，例如 MAX/MIN/SUM 等。如果您希望合计 Qty 为 PieceType，那么您似乎想要使用 SUM，因此您的代码将是:

SET group_concat_max_len=4294967294;
SET @COLUMNS = NULL;


/* Build columns to pivot */
SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
            'SUM(CASE WHEN jp.PieceType = ''', 
            jp.PieceType ,
            ''' THEN jp.QTY ELSE 0 END) AS `',
            jp.PieceType, '`'
        )
) INTO @COLUMNS
FROM job_pieces jp;

/* Build full query */
SET @SQL = CONCAT(
        'SELECT    
                c.Name,
                ',@COLUMNS, '
        FROM customer c
        LEFT JOIN job_new jn ON c.JobID = jn.ID
        LEFT JOIN job_pieces jp ON c.JobID = jp.JobID
        WHERE c.Company_ID = 123
        GROUP BY c.Name'
);

/* Prepare and execute the query*/
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见SQL Fiddle with Demo