0

the data "123,456,332,123,567,448......"

how to select the data and get the list like :

id ,count(*) 


+---+-------+-------+
|   |  ID   | Count |
+---+-------+-------+
| 1 | 123   | 2     |
| 2 | 456   | 1     |
| 3 | 332   | 1     |
| 4 | 567   | 1     |
| 5 | 448   | 1     |
+---+-------+-------+

I try to use 

from
             (select 1 n union all select 2 union all select 3 union all 
                select 4 union all select 5 union all select 6 union all
                select 7 union all select 8 union all select 9 union all 
                select 10 union all select 11 union all select 12 union all ...

but if the comma over 1000 or more, how to get the split list ?

---- edit GMT +8 3/14 14:35 ---

I had look up this post and the solution did not match.

Can you split/explode a field in a MySQL query?

if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode

even the solution can try to 

    SELECT 1 + units.i + tens.i * 10 AS aNum
FROM (SELECT 0 AS i 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) units
CROSS JOIN (SELECT 0 AS i 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) tens

but if we don't know how many comma that i can't get the right way

Eric T
  • 11
  • 2
  • 1
    Actually, I find none of the answers in the duplicate link to be very obvious, but the bottom line is that you should _not_ be storing CSV lists of numbers in your tables. – Tim Biegeleisen Mar 14 '18 at 01:34
  • Well, not if you want to split them with SQL. If you want to store a string with commas in it, go ahead, but treat it as an irreducible string in SQL. – Bill Karwin Mar 14 '18 at 02:51
  • in actuly i need to get the data and left join the others, so i want to get the way to split the str – Eric T Mar 14 '18 at 06:44

0 Answers0