mysql将行显示成列_mysql – 在表中将行显示为列

我有一张像下面这样的表

CREATE TABLE Statistics(Stat_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Period VARCHAR(55),

Location VARCHAR(255),

Rate_per_SqFt INT)

表中的数据是

INSERT INTO Statistics(Period, Location, Rate_per_SqFt)

VALUES('June', 'Location A', 2500),

('June', 'Location B', 2740),

('June', 'Location C', 3200),

('July', 'Location A', 2650),

('July', 'Location B', 2800),

('July', 'Location C', 3250),

('August', 'Location A', 2750),

('August', 'Location B', 2950),

('August', 'Location C', 3230),

('October', 'Location A', 2950),

('October', 'Location B', 3950),

('October', 'Location C', 3530);

我希望输出中显示的特定月份的行作为下面的单独列

Period Location A Location B Location C

June 2500 2740 3200

July 2650 2800 3250

August 2750 2950 3230

October 2950 3950 3530

如何使用Query执行此操作

解决方法:

SELECT Period,

MAX(CASE WHEN Location = 'Location A' THEN Rate_per_SqFt ELSE NULL END) `Location A`,

MAX(CASE WHEN Location = 'Location B' THEN Rate_per_SqFt ELSE NULL END) `Location B`,

MAX(CASE WHEN Location = 'Location C' THEN Rate_per_SqFt ELSE NULL END) `Location C`

FROM statistics

GROUP BY Period

如果您的位置数量未知,则首选动态SQL

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'MAX(CASE WHEN Location = ''',

Location,

''' then Rate_per_SqFt ELSE NULL end) AS ',

CONCAT('`',Location,'`')

)

) INTO @sql

FROM statistics;

SET @sql = CONCAT('SELECT Period, ', @sql, '

FROM Statistics

GROUP BY Period');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

标签：mysql,sql,select,mysqli

来源： https://codeday.me/bug/20190620/1246180.html