Méthode de conversion de lignes et de colonnes de données de table Mysql

Pendant le processus de développement, pour des raisons historiques ou de performances, il est nécessaire de convertir les données de colonne du tableau en données de ligne, ou de convertir les données de ligne en données de colonne à utiliser. des lignes et des lignes de données de table MySQL, et fournir des exemples de démonstration complets et des compétences SQL.

1. Convertir des lignes en colonnes

Créer un tableau et des données de données de test

CREATE TABLE `option` ( `category_id` int(10) unsigned NOT NULL COMMENT '分类id', `name` varchar(20) NOT NULL COMMENT '名称', KEY `category_id` (`category_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `option` (`category_id`, `name`) VALUES
(1, '大'),
(1, '中'),
(1, '小'),
(2, '奔驰'),
(2, '宝马'),
(3, '2015'),
(3, '2016'),
(3, '2017'),
(3, '2018'),
(4, '1m'),
(4, '2m');mysql> select * from `option`;
+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

Après avoir converti des lignes en colonnes , attendez Les résultats suivants sont obtenus

+-------------+---------------------+| category_id | name                |
+-------------+---------------------+|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |
+-------------+---------------------+

conversion de ligne en colonne, qui peut être obtenue en utilisant la fonction group_concat() combinée avec group by.

La fonction group_concat() peut obtenir la valeur de connexion de la combinaison d'expressions. Le séparateur par défaut est la virgule, qui peut être définie sur d'autres séparateurs via separator.

Remarque : La fonction group_concat() a une limite de longueur sur le résultat renvoyé. La valeur par défaut est de 1024 octets, mais c'est suffisant pour les situations normales.

Pour l'utilisation de la fonction group_concat(), merci de vous référer à mon article précédent : "Instructions pour l'utilisation des fonctions mysql concat et group_concat"

Résultats de l'exécution :

mysql> select category_id,group_concat(name) as name from `option` group by category_id order by category_id;
+-------------+---------------------+| category_id | name                |
+-------------+---------------------+|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |
+-------------+---------------------+

2. Transfert de colonnes

Créer une table et des données de test

CREATE TABLE `option2` ( `category_id` int(10) unsigned NOT NULL COMMENT '分类id', `name` varchar(100) NOT NULL COMMENT '名称集合') ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `option2` (`category_id`, `name`) VALUES
(1, '大,中,小 '),
(2, '奔驰,宝马'),
(3, '2015,2016,2017,2018'),
(4, '1m,2m');mysql> select * from `option2`;
+-------------+---------------------+| category_id | name                |
+-------------+---------------------+|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 ||           4 | 1m,2m               |
+-------------+---------------------+

Après avoir converti des colonnes en lignes, attendez-vous aux résultats suivants

+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

La conversion de colonnes en lignes est plus compliquée que la conversion de lignes en colonnes pour les données dont la colonne. le contenu est séparé par des délimiteurs, nous pouvons utiliser La fonction substring_index() effectue une sortie de segmentation et est combinée avec le Produit cartésien pour implémenter le bouclage.

select a.category_id,substring_index(substring_index(a.name,&#39;,&#39;,b.category_id),&#39;,&#39;,-1) as name from `option2` as ajoin `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,&#39;,&#39;,&#39;&#39;))+1)order by a.category_id,b.category_id;

Résultat de l'exécution :

mysql> select a.category_id,substring_index(substring_index(a.name,&#39;,&#39;,b.category_id),&#39;,&#39;,-1) as name from `option2` as a    -> join `option2` as b on b.category_id<=(length(a.name) - length(replace(a.name,&#39;,&#39;,&#39;&#39;))+1)    -> order by a.category_id,b.category_id;
+-------------+--------+| category_id | name   |
+-------------+--------+|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     ||           4 | 2m     |
+-------------+--------+

Cet article explique la méthode de conversion des lignes et des colonnes des données d'une table MySQL , et plus de contenu connexe Veuillez faire attention au site Web chinois php.

Recommandations associées :

Comment afficher rapidement les fichiers de configuration dans nginx

php Plusieurs nombres unidimensionnels sont combinés en deux méthodes pour les tableaux dimensionnels

méthodes php pour renvoyer plusieurs colonnes spécifiées dans le tableau

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!