Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to achieve row transfer operation in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The basic structure of the table

Transform the front table structure:

CREATE TABLE orders (

Orders_id INT NOT NULL AUTO_INCREMENT COMMENT 'order ID'

Good_ids VARCHAR NOT NULL COMMENT 'Commodity ID string'

PRIMARY KEY (orders_id)

)

INSERT INTO orders VALUES (NULL,'1, 2, 3, 4, 5')

INSERT INTO orders VALUES (NULL,'11, 12, 13, 13, 14, 15')

INSERT INTO orders VALUES (NULL,'21, 22, 22, 23, 24, 25')

The table structure after transformation:

-- orders table remains unchanged

CREATE TABLE orders (

Orders_id INT NOT NULL AUTO_INCREMENT COMMENT 'order ID'

Good_ids VARCHAR NOT NULL COMMENT 'Commodity ID string'

PRIMARY KEY (orders_id)

)

-- list of new orders

CREATE TABLE order_goods (

Order_goods_id INT NOT NULL AUTO_INCREMENT COMMENT 'order goods ID'

Orders_id INT NOT NULL COMMENT 'order ID'

Goods_id INT NOT NULL COMMENT 'merchandise ID'

PRIMARY KEY (order_goods_id)

)

Realize row to column

Here we need to use a table (mysql.help_topic) with contiguous ID to do the association, so that we can divide the commodity ID.

SELECT o.orders_id

SUBSTRING_INDEX (SUBSTRING_INDEX (o.good_ids,', h.help_topic_id),',',-1)

FROM orders AS o

JOIN mysql.help_topic AS h

ON h.help_topic_id 0

ORDER BY o.orders_id

Decompose SQL for interpretation

Our goal here is to get the second item ID in the commodity ID string.

Use a comma (',') to split the good_ids to view the first two elements (good_id):

SELECT o.orders_id

SUBSTRING_INDEX (o.good_ids,', 2)

FROM orders AS o

+-- +

| | orders_id | SUBSTRING_INDEX (o.good_ids,',', 2) |

+-- +

| | 1 | 1Pol 2 |

| | 2 | 11pr 12 |

| | 3 | 21, 022 | |

+-- +

With the string of the first two elements obtained above, split the comma (',') again and get the last element.

SELECT o.orders_id

SUBSTRING_INDEX (

SUBSTRING_INDEX (o.good_ids,', 2)

','

-1

) AS good_id

FROM orders AS o

+-+ +

| | orders_id | good_id |

+-+ +

| | 1 | 2 |

| | 2 | 12 |

| | 3 | 22 |

+-+ +

The final SQL simply changes the specified second element into a dynamic one. And dynamically changing numbers are realized by associating mysql.help_topic.

Tip: the reason for using mysql.help_topic here is that it has continuous help_topic_id from 0 to 629. Of course, you can also create your own table and insert continuous data as an associated table.

SELECT o.orders_id

SUBSTRING_INDEX (SUBSTRING_INDEX (o.good_ids,', h.help_topic_id),',',-1) AS good_id

FROM orders AS o

JOIN mysql.help_topic AS h

ON h.help_topic_id 0

ORDER BY o.orders_id

+-+ +

| | orders_id | good_id |

+-+ +

| | 1 | 1 |

| | 1 | 2 |

| | 1 | 3 |

| | 1 | 4 |

| | 1 | 5 |

| | 2 | 15 |

| | 2 | 11 |

| | 2 | 12 |

| | 2 | 13 |

| | 2 | 14 |

| | 3 | 22 |

| | 3 | 23 |

| | 3 | 24 |

| | 3 | 25 |

| | 3 | 21 |

+-+ +

The above is how to achieve row-to-column operation in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.