mysql 实现行转列
时间: 2023-12-23 20:27:49 浏览: 32
以下是两种MySQL实现行转列的方法:
1. 使用CASE语句和聚合函数:
```mysql
SELECT Name,
MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
MAX(CASE WHEN subject = '物理' THEN score END) AS 物理
FROM (
SELECT Name, SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', n), ':', -1) AS score,
SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', n), ':', 1) AS subject
FROM cj3
JOIN (
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
) AS numbers
ON CHAR_LENGTH(成绩) - CHAR_LENGTH(REPLACE(成绩, ',', '')) >= n - 1
) AS subquery
GROUP BY Name;
```
2. 使用GROUP_CONCAT函数和字符串函数:
```mysql
SELECT Name,
SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', 1), ':', -1) AS 语文,
SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', 2), ':', -1) AS 数学,
SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', 3), ':', -1) AS 物理
FROM (
SELECT Name, GROUP_CONCAT(CONCAT(subject, ':', score) ORDER BY subject) AS 成绩
FROM (
SELECT Name, SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', n), ':', -1) AS score,
SUBSTRING_INDEX(SUBSTRING_INDEX(成绩, ',', n), ':', 1) AS subject
FROM cj3
JOIN (
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
) AS numbers
ON CHAR_LENGTH(成绩) - CHAR_LENGTH(REPLACE(成绩, ',', '')) >= n - 1
) AS subquery
GROUP BY Name
) AS subquery2;
```
相关推荐
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)