I have this table
| id | subOperation |
|---|---|
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test1,test2 |
| 5 | test1,test3 |
I need help, to make a query which gives me the following result
| id | idSubOperation | idElement |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 1 |
| 5 | 4 | 2 |
| 6 | 5 | 1 |
| 7 | 5 | 3 |
When I have one "," I need to assemble the id How can I do it?
The idElement of the id is also obtained, but taking into account the name of the suboperation.And it can be more than one ,
Assuming there are at most two elements in the subOperation (as in your example), you can use union all to bring together the different components of the final result set:
select row_number() over (order by id_suboperation, id_element) as id, tt.*
from ((select id as id_suboperation, id as id_element
from t
where t.subOperation not like '%,%'
) union all
(select t.id, tsub.id
from t join
t tsub
on substring_index(t.subOperation, ',', 1) = tsub.subOperation
where t.subOperation like '%,%'
) union all
(select t.id, tsub.id
from t join
t tsub
on substring_index(t.subOperation, ',', -1) = tsub.subOperation
where t.subOperation like '%,%'
)
) tt;
Here is a db<>fiddle.
I should note that this can pretty easily be extended to longer lists of values, but only two seem necessary for this question.
EDIT:
The only issue in MySQL 5.6 is assigning the row number. You can use:
set @rn = 0;
select (@rn := @rn + 1) as id, tt.*
. . .
https://dev.mysql.com/doc/refman/8.0/en/with.html
create table tab1 as
select 1 as id, 'test1' as subOperation union all
select 2 as id, 'test2' as subOperation union all
select 3 as id, 'test3' as subOperation union all
select 4 as id, 'test1,test2' as subOperation union all
select 5 as id, 'test1,test3' union all
select 6 as id, 'test1,test3,test2';
select * from tab1;
with recursive t1(id, suboperation, lv) as (
select t0.id, t0.suboperation, 1 level from tab1 t0
union all
select t2.id, t2.suboperation, t1.lv + 1 from tab1 t2, t1
where t2.id = t1.id
and t1.lv <= length(t1.suboperation) - length(replace(t2.suboperation, ',', ''))
),
tab2 as (
select row_number() over(order by t1.id) id,
t1.id idSubOperation,
regexp_substr(t1.suboperation, '[^,]+', 1, row_number() over(partition by t1.id)) suboperation
from t1
)
select t1.*,
t2.id idElement
from tab2 t1,
(select * from tab1 t1 where t1.subOperation not regexp ',') t2
where t1.suboperation = t2.suboperation
order by t1.id
;
mysql5.6 ,A little complicated, but can be simplified with a custom function
#drop FUNCTION split_substr;
CREATE FUNCTION split_substr(p_src varchar(2000), p_split VARCHAR(200), p_num int)
RETURNS varchar(2000)
NO SQL
BEGIN
declare f_res VARCHAR(2000);
while p_num > 0 do
select case when instr(p_src, p_split) = 0 and length(p_src) > 0 then p_src else
substr(p_src, 1, instr(p_src, p_split) - length(p_split))
end,
substr(p_src, case when instr(p_src, p_split) = 0 then length(p_src) + 1 else
instr(p_src, p_split) + length(p_split)
end)
into f_res, p_src;
set p_num = p_num - 1;
end while;
RETURN f_res;
end;
select t1.*,
t2.id,
@rownum:=@rownum+1
from (
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 1) name from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 2) from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 3) from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 4) from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 5) from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 6) from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 7) from tab1 t1 union all
select t1.id, t1.subOperation, split_substr(t1.subOperation, ',', 8) from tab1 t1
) t1,
(select * from tab1 t1 where t1.subOperation not regexp ',') t2,
(select @rownum:=0) t3
where t1.name = t2.subOperation
and length(t1.name) > 0
order by t1.id
;
Also possible using PREPARED STATEMENT
SET @sql := NULL;
SET @sql := (SELECT CONCAT('SELECT ROW_NUMBER() OVER (ORDER BY id_suboperation,
id_element) AS id,
id_suboperation, id_element
FROM (',GROUP_CONCAT(qv SEPARATOR ' union all '),' ) sq
GROUP BY id_suboperation, id_element
ORDER BY id_suboperation, id_element;')
FROM
(SELECT CONCAT("SELECT id AS id_suboperation,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REPLACE(subOperation,'test',''),',',",idx,"),',',-1) AS id_element
FROM mytable") qv FROM mytable a CROSS JOIN
(WITH RECURSIVE wordCount AS (
SELECT 1 IDX,
(LENGTH(subOperation)-LENGTH(REPLACE(subOperation,',','')))+1 AS MAXCOUNT
FROM mytable UNION ALL
SELECT IDX+1, MAXCOUNT FROM wordCount WHERE IDX+1 <= MAXCOUNT)
SELECT IDX FROM wordCount
GROUP BY IDX) b
GROUP BY qv) c);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
At least with new data being added, you don't need to change the statement.
P/S: I personally like @nayi224's answer though.
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a1f8142fdc9a66b72a455e3451029bf
Update:
For older MySQL version that don't support WITH and ROW_NUMBER():
SET @sql := NULL;
SET @sql := (SELECT CONCAT('SELECT (@rownum := @rownum+1) AS Rownum,
id_suboperation, id_element
FROM
(SELECT *
FROM (',GROUP_CONCAT(qv SEPARATOR ' union all '),' ) sq
GROUP BY id_suboperation, id_element) vr
CROSS JOIN (SELECT @rownum := 0) rn
ORDER BY id_suboperation, id_element;')
FROM
(SELECT CONCAT("SELECT id AS id_suboperation,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REPLACE(subOperation,'test',''),',',",idx,"),',',-1) AS
id_element
FROM mytable") qv FROM mytable a CROSS JOIN
(SELECT 1 IDX UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4) b
GROUP BY qv) c);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
This will return @sql as following:
SELECT (@rownum := @rownum+1) AS Rownum, id_suboperation, id_element
FROM (SELECT * FROM (SELECT id AS id_suboperation,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REPLACE(subOperation,'test',''),',',1),',',-1) AS id_element
FROM mytable union all SELECT id AS id_suboperation,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REPLACE(subOperation,'test',''),',',2),',',-1) AS id_element
FROM mytable union all SELECT id AS id_suboperation,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REPLACE(subOperation,'test',''),',',3),',',-1) AS id_element
FROM mytable union all SELECT id AS id_suboperation,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REPLACE(subOperation,'test',''),',',4),',',-1) AS id_element
FROM mytable ) sq
GROUP BY id_suboperation, id_element) vr
CROSS JOIN (SELECT @rownum := 0) rn
ORDER BY id_suboperation, id_element;
As for your question "And also, what happens when all the words don't start with test?" , if the string value is consistent, then you can just replace test with a different word from this part:
....
REPLACE(subOperation,'test',''),',',",idx,"),',',-1) AS id_element
^^^^
here
....
If the string parts are not consistent but the numerical value is always at the end, you may try using REVERSE() with CAST or just REVERSE(REVERSE(string)+0). The conversion from string to numbers only I referred from this answer . So, the query for setting @sql might be something like this at the end:
SET @sql := (SELECT CONCAT('SELECT ROW_NUMBER() OVER (ORDER BY id_suboperation,
id_element) AS id,
id_suboperation, id_element
FROM (',GROUP_CONCAT(qv SEPARATOR ' union all '),' ) sq
GROUP BY id_suboperation, id_element
ORDER BY id_suboperation, id_element;')
FROM
(SELECT CONCAT("SELECT id AS id_suboperation,
REVERSE(REVERSE(SUBSTRING_INDEX(
SUBSTRING_INDEX(subOperation,',',",idx,"),',',-1))+0) AS id_element
FROM mytable") qv FROM mytable a CROSS JOIN
(SELECT 1 IDX UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4) b
GROUP BY qv) c);
The REVERSE() function is repeated because if the string value is like this test53, the first reverse will make it as 35tset. Then after using CAST() or +0 on it, the number value will become 35 instead of 53 from the original string value. Therefore, to get the correct number from the original string, it has to be reversed again.
