MySQL comma split field row and column conversion tips

SELECT Group_concat (REPLACE (Path, '/', ', ')) as path from Department b WHERE Department_type = 1

Execution Result:

Sql:

Select DISTINCT Substring_index (Substring_index (A.path, ', ', b.help_topic_id + 1), ', ', -1)   from   (select Group_ CONCAT (REPLACE (Path, '/', ', ')) as path from Department b WHERE department_type = 1) ajoin  mysql.help_topic b  

Execution Result:

Principle Analysis:

The most basic principle of this join is the Cartesian product. This is the way to implement loops.

Analysis:

Length (A.path)-Length (replace (A.path, ', ', ')) +1 indicates the number of times the partition needs to be looped after the comma is split.

Join process:

Loop based on ID

{

Judging: I whether <= n

Get the data closest to the first comma, i.e. Substring_index (Substring_index (A.path, ', ', b.help_topic_id), ', ',-1)

}

The disadvantage of this approach is that we need a stand-alone table with sequential sequences. and the maximum value of a continuous series must be greater than the number of values that match the split.

For example, if you have a row with a path with 100 comma-separated values, then our table needs to have at least 100 contiguous rows.

Of course, MySQL also has a ready-made list of contiguous numbers available. If mysql.help_topic:help_topic_id a total of 504 values, generally can meet the majority of demand.

MySQL comma split field row and column conversion tips