更新时间:2023-01-29 21:10:49
静态查询(就Bob和Sue而言)可能看起来像这样
A static query (as far as only Bob and Sue are concerned) might look like this
SELECT subject,
MAX(CASE WHEN name = 'Bob' THEN grade END) `Bob`,
MAX(CASE WHEN name = 'Sue' THEN grade END) `Sue`
FROM table1
GROUP BY subject
现在可以使用其他动态SQL来解释其他名称
Now to be able to account for other names use dynamic SQL like this
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN name = ''', name,
''' THEN grade END) `', name, '`'))
INTO @sql
FROM table1;
SET @sql = CONCAT('SELECT subject, ', @sql, '
FROM table1
GROUP BY subject');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
输出:
| SUBJECT | BOB | SUE |
|-----------|--------|--------|
| Biology | D | (null) |
| Geography | (null) | C |
| History | B | C |
| Language | C | (null) |
| Math | A | A |
| Music | (null) | A |
这里是 SQLFiddle 演示
Here is SQLFiddle demo
您可以将其包装到存储过程中,以简化调用端的操作
You can wrap it into a stored procedure to simplify things on the calling end
DELIMITER $$
CREATE PROCEDURE sp_grade_report()
BEGIN
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN name = ''', name,
''' THEN grade END) `', name, '`'))
INTO @sql
FROM table1;
SET @sql = CONCAT('SELECT subject, ', @sql, '
FROM table1
GROUP BY subject');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
样品用量:
CALL sp_grade_report();
这里是 SQLFiddle 演示
Here is SQLFiddle demo