列名为数字时MariaDB数据透视不起作用

Question

我有这样的代码(我从其他SO线程中获得)：

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(property_name = ''',
property_name,
''', value, NULL)) AS ',
property_name
)
) INTO @sql
FROM properties;
#INNER JOIN combustibles ON properties.property_name = combustibles.id_combustible
SET @sql = CONCAT('SELECT anio, mes, ', @sql, ' FROM properties GROUP BY anio, mes');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

它在做他们的工作，我的桌子是：

table: properties
id  year    month   property_name   value
1   1       ene     a               345
2   1       ene     b               545
3   1       ene     c               65
4   1       feb     a               32
5   1       feb     b               57
6   1       feb     c               444
7   2       ene     a               123
8   2       ene     b               333
9   2       ene     c               12
10  2       feb     a               56
11  2       feb     b               565
12  2       feb     c               34

我得到了我需要的东西：

year    month   a   b   c
1       ene     345 545 65
1       feb     32  57  444
2       ene     123 333 12
2       feb     56  565 34

但问题是，如果"property_name"的值只有NUMBERS，就像这样：

table: properties
id  year    month   property_name   value
1   1       ene     1               345
2   1       ene     2               545
3   1       ene     3               65

等等。。。

我得到这个错误：

Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1,MAX(IF(property_name = '2', value, NULL)) AS 2,MAX(IF(property_name = '3', val' at line 1

我需要那些"property_name"值是数字，因为这是一个例子，但对于我的真实数据，这些值是来自另一个表的id。有什么提示吗？

Answer 1

CREATE TABLE IF NOT EXISTS `properties` (
`id` int(6) unsigned NOT NULL,
`year` int(6) unsigned NOT NULL,
`month` varchar(200) NOT NULL,
`property_name` varchar(200) NOT NULL,
`value` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

<blockquote\

INSERT INTO `properties` (`id`, `year`, `month`, `property_name`, `value`) VALUES
("1","1","a","345","ene"),
("2","1","b","545","ene"),
("3","1","c","65","ene"),
("4","1","a","32","feb"),
("5","1","b","57","feb"),
("6","1","c","444","feb"),
("7","2","a","123","ene"),
("8","2","b","333","ene"),
("9","2","c","12","ene"),
("10","2","a","56","feb"),
("11","2","b","565","feb"),
("12","2","c","34","feb");

>

<blockquote\

select group_CONCAT(p)
from (
select distinct concat('MAX(IF(property_name = '''
,property_name
,''', value, NULL)) AS `'
,property_name
,'`') p
from properties
) d

>

|group_CONCAT(p)||：----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------||MAX(IF(property_name='345'，value，NULL))AS `345'，MAX(IF)AS‘123’，MAX(IF(property_name='333'，value，NULL))AS `333'，MAX(IF)(property_name='12'，value，NULL)AS `12'，MAX