在MySQL数据库中,实现行列互转和字符串拆分是常见的需求。以下是实现这些操作的几种方法:
行转列通常用于将多行数据转换为多列数据。假设有一个表 sales
,结构如下:
CREATE TABLE sales (
id INT,
product VARCHAR(50),
year INT,
amount DECIMAL(10, 2)
);
假设数据如下:
INSERT INTO sales (id, product, year, amount) VALUES
(1, 'A', 2021, 100),
(2, 'B', 2021, 150),
(3, 'A', 2022, 200),
(4, 'B', 2022, 250);
要将每年的销售额转换为列,可以使用 CASE
语句:
SELECT
product,
SUM(CASE WHEN year = 2021 THEN amount ELSE 0 END) AS '2021',
SUM(CASE WHEN year = 2022 THEN amount ELSE 0 END) AS '2022'
FROM sales
GROUP BY product;
结果将是:
product | 2021 | 2022 |
---|---|---|
A | 100 | 200 |
B | 150 | 250 |
列转行通常用于将多列数据转换为多行数据。假设有一个表 sales_pivot
,结构如下:
CREATE TABLE sales_pivot (
product VARCHAR(50),
`2021` DECIMAL(10, 2),
`2022` DECIMAL(10, 2)
);
假设数据如下:
INSERT INTO sales_pivot (product, `2021`, `2022`) VALUES
('A', 100, 200),
('B', 150, 250);
要将列转换为行,可以使用 UNION ALL
:
SELECT product, '2021' AS year, `2021` AS amount FROM sales_pivot
UNION ALL
SELECT product, '2022' AS year, `2022` AS amount FROM sales_pivot
ORDER BY product, year;
结果将是:
product | year | amount |
---|---|---|
A | 2021 | 100 |
A | 2022 | 200 |
B | 2021 | 150 |
B | 2022 | 250 |
MySQL 本身没有直接的字符串拆分函数,但可以通过一些技巧实现。
SUBSTRING_INDEX
函数假设有一个字符串 'apple,banana,cherry'
,我们想将其拆分为多行。
SET @str = 'apple,banana,cherry';
SET @delimiter = ',';
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(@str, @delimiter, n), @delimiter, -1) AS value
FROM
(SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
WHERE
n <= LENGTH(@str) - LENGTH(REPLACE(@str, @delimiter, '')) + 1;
结果将是:
value |
---|
apple |
banana |
cherry |
如果需要更复杂的字符串拆分,可以编写存储过程:
DELIMITER //
CREATE PROCEDURE SplitString(IN inputStr VARCHAR(255), IN delimiter CHAR(1))
BEGIN
DECLARE pos INT DEFAULT 1;
DECLARE token VARCHAR(255);
DECLARE done INT DEFAULT 0;
WHILE done = 0 DO
SET pos = LOCATE(delimiter, inputStr);
IF pos = 0 THEN
SET token = inputStr;
SET done = 1;
ELSE
SET token = SUBSTRING(inputStr, 1, pos - 1);
SET inputStr = SUBSTRING(inputStr, pos + 1);
END IF;
SELECT token;
END WHILE;
END //
DELIMITER ;
CALL SplitString('apple,banana,cherry', ',');
结果将是:
token |
---|
apple |
banana |
cherry |
CASE
语句实现行转列,使用 UNION ALL
实现列转行。SUBSTRING_INDEX
函数或编写存储过程来实现字符串拆分。这些方法可以帮助你在MySQL中实现行列互转和字符串拆分的需求。