I have a table as below:-
id | columnA | columnB | columnC 1 7234 3432 3245 2 1237 1123 6546 3 4421 9371 7324 4 1244 1424 4237
How can I select combination records when I pass in “2374” in my select statement?
My expected result should be
id | columnA | columnB | columnC 1 7234 3432 3245 3 4421 9371 7324 4 1244 1424 4237
Advertisement
Answer
It is not easy to read, but it works:
set @s = '2374'; select * from tablename where id in ( select distinct t1.id from ( select t.id, t.abc, group_concat(t.d order by t.d separator '') col from ( select id, abc, substring(col, n, 1) d from ( select id, 'A' abc, columnA col from tablename union all select id, 'B', columnB from tablename union all select id, 'C', columnC from tablename ) t cross join ( select 1 n union all select 2 union all select 3 union all select 4 ) v ) t group by t.id, t.abc ) t1 inner join ( select group_concat(t.d order by t.d separator '') s from ( select substring(@s, 1, 1) d union all select substring(@s, 2, 1) union all select substring(@s, 3, 1) union all select substring(@s, 4, 1) ) t ) t2 on t2.s = t1.col )
What this code does is:
sorts the digits inside the column’s value, so 4237
becomes 2347
and compares against the (also) sorted sorted 2374
–> 2347
.
For this sorting I used group_concat()
.
See the demo.
Results:
| id | columnA | columnB | columnC | | --- | ------- | ------- | ------- | | 1 | 7234 | 3432 | 3245 | | 3 | 4421 | 9371 | 7324 | | 4 | 1244 | 1424 | 4237 |