mysql - 当列名是数字时，MariaDB Pivot 不起作用

Question

这个问题已经有答案了:

When to use single quotes, double quotes, and backticks in MySQL (13 个回答)

已关闭 5 年前。

我有这个代码(我从其他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 是:

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”的值只是数字，如下所示:

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;

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");

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(property_name = '545', value, NULL)) AS `545`,MAX(IF(property_name = '65', value, NULL)) AS `65`,MAX(IF(property_name = '32', value, NULL)) AS `32`,MAX(IF(property_name = '57', value, NULL)) AS `57`,MAX(IF(property_name = '444', value, NULL)) AS `444`,MAX(IF(property_name = '123', value, NULL)) AS `123`,MAX(IF(property_name = '333', value, NULL)) AS `333`,MAX(IF(property_name = '12', value, NULL)) AS `12`,MAX(IF(property_name = '56', value, NULL)) AS `56`,MAX(IF(property_name = '565', value, NULL)) AS `565`,MAX(IF(property_name = '34', value, NULL)) AS `34` |

dbfiddle here