Mysql column Change Group_concat function, with row to column (original)

1, the normal situation.

SELECT  from Nt_mainnum

2. Using the Group_concat function

Select  from Nt_mainnum

3. Use Substring_index and Cross join to split the numbers inside the column.

Method One (online Query method):

Configuration table:

    CREATE TABLEDigits (digitINT(1)); INSERT  intodigitsVALUES        (0),        (1),        (2),        (3),        (4),        (5),        (6),        (7),        (8),        (9); CREATE TABLESequence (seqINT(3)); INSERT  intoSequence (SELECTD1.digit+D2.digit* Ten         fromdigits D1 Cross JOINdigits D2);

The result of the configuration table sequence is a column number of 0-99:

Sql:

SELECTSubstring_index (Substring_index (Joineventids,',', seq),',',- 1) Joineventids fromsequence Cross JOINNt_mainnumWHEREseqbetween 1 and (    SELECT        1 +LENGTH (Joineventids)-LENGTH (REPLACE(Joineventids,',',"')        ))

Method Two (oneself do not want to build a table, diagram easy): Replace sequence with SELECT @rownum: [email protected]+1 as seq from (SELECT @rownum: =0) R, Nt_mainnum LIMIT 0,100) , this table needs to be more than 100 bars.

SELECTSubstring_index (Substring_index (Joineventids,',', seq),',',- 1) Joineventids from        (SELECT @rownum:=@rownum+1  asSeq from(SELECT @rownum:=0) R, Nt_mainnum LIMIT0, -) b Cross JOINNt_mainnumWHEREseqbetween 1     and (        SELECT            1 +LENGTH (Joineventids)-LENGTH (REPLACE(Joineventids,',',"'))) 

The results are:

Mysql column Change Group_concat function, with row to column (original)