表格结构:
1_column 2_column
-----------------------------
1 data 1; data 2
2 data 3
3 data 1; data 15; data 6
4 data 2; data 3; data 16
5 data 3
查询:SELECT DISTINCT 2_column FROM table ORDER BY 1_column
返回的输出为:4行。
data 1; data 2
data 3
data 1; data 15, data 6
data 2; data 3; data 16
现在我想分解列2_column
的值,如数据1、数据2、数据3等,然后在分解的值中分解SELECT DISTINCT
。如何在考虑性能的同时,仅使用MySQL来实现这一点?
编辑:预期结果:
data 1
data 2
data 3
data 6
data 15
data 16
分解的DISTINCT值。6排。
显然,您需要规范化数据。但我想你可以用这样的东西作为实现这一目标的一步。。。
SELECT * FROM my_table;
+---------+-------------------------+
| column1 | column2 |
+---------+-------------------------+
| 1 | data 1; data 2 |
| 2 | data 3 |
| 3 | data 1; data 15; data 6 |
| 4 | data 2; data 3; data 16 |
| 5 | data 3 |
+---------+-------------------------+
SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+
SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(column2,';',i),';',-1)) x
FROM my_table
, ints i
HAVING x <> '';
+---------+
| x |
+---------+
| data 1 |
| data 3 |
| data 2 |
| data 15 |
| data 6 |
| data 16 |
+---------+
琐碎的方法如下:
DELIMITER $$
DROP PROCEDURE IF EXISTS getresults$$
CREATE PROCEDURE getresults()
BEGIN
DECLARE ite INT;
SET @s="";
select MAX(LENGTH(2_column)-LENGTH(REPLACE(2_column, ";", "")))+1 max
FROM mytable INTO ite;
WHILE (ite>0) DO
SET @s=concat(@s,
"UNION SELECT ltrim(substring_index(
SUBSTRING_INDEX(2_column, ';', ",ite,"), ';', -1)) data
FROM mytable ");
SET ite:=ite-1;
END WHILE;
SET @s=mid(@s, 6);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
call getresults();
+---------+
| data |
+---------+
| data 2 |
| data 3 |
| data 6 |
| data 16 |
| data 15 |
| data 1 |
+---------+
我确实喜欢Strawberry答案,它比我以前的存储过程更简单
如果您假设您的"伪列"不会多于行,那么您可以使用下面的指令来绕过创建int表的限制:
SET @a:=0;
SELECT DISTINCT TRIM(SUBSTRING_INDEX(
SUBSTRING_INDEX(2_column,';',a.nb),';',-1)) data
FROM mytable,
(SELECT @a:=@a+1 nb
FROM mytable
WHERE @a<
(SELECT MAX(LENGTH(m1.2_column)
-LENGTH(REPLACE(m1.2_column, ";", "")))+1 max
FROM mytable m1)) a;