mysql - Rewrite to not include subquery in FROM? -

mysql - Rewrite to not include subquery in FROM? -

a great fellow helped me developing next statement. however, in mysql - cannot save view subquery in clause. suggestions o nhow rewrite can saved mysql server?

select t.idpatternmetadata, substring_index(substring_index(t.skeywords, ',', n.n), ',', -1) color , count(*) counts tblpatternmetadata t cross bring together ( select a.n + b.n * 10 + 1 n (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) ,(select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b order n ) n n.n <= 1 + (length(t.skeywords) - length(replace(t.skeywords, ',', ''))) grouping color

thanks in advance!

one alternative create table contains 100 integer values, , reference table in query.

create table n (n int unsigned primary key); insert n (n) select a.n + b.n * 10 + 1 n ( select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) cross bring together ( select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) b order 1;

then rewrite query reference table in place of inline view:

select t.idpatternmetadata, substring_index(substring_index(t.skeywords, ',', n.n), ',', -1) color , count(*) counts tblpatternmetadata t bring together n on n.n <= 1 + (length(t.skeywords) - length(replace(t.skeywords, ',', ''))) grouping color

mysql sql subquery