mysql 行转列

新建表

CREATE TABLE `data_source`  (
  `Id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `TableName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`Key`, `Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

插入数据

INSERT INTO `data_source` VALUES ('1', 'patient', 'address', '南疆路71号');
INSERT INTO `data_source` VALUES ('5', 'patient', 'address', '离厂路98号');
INSERT INTO `data_source` VALUES ('1', 'sequence', 'age', '18');
INSERT INTO `data_source` VALUES ('2', 'sequence', 'age', '26');
INSERT INTO `data_source` VALUES ('1', 'patient', 'city', '成都');
INSERT INTO `data_source` VALUES ('2', 'patient', 'city', '北京');
INSERT INTO `data_source` VALUES ('3', 'patient', 'city', '北京');
INSERT INTO `data_source` VALUES ('4', 'patient', 'city', '上海');
INSERT INTO `data_source` VALUES ('5', 'patient', 'city', '深圳');
INSERT INTO `data_source` VALUES ('6', 'patient', 'city', '南京');
INSERT INTO `data_source` VALUES ('7', 'patient', 'city', '西安');
INSERT INTO `data_source` VALUES ('8', 'patient', 'city', '杭州');
INSERT INTO `data_source` VALUES ('1', 'sequence', 'name', '张三');
INSERT INTO `data_source` VALUES ('2', 'sequence', 'name', '李思');
INSERT INTO `data_source` VALUES ('2', 'patient', 'plate', '川A87WQ1');
INSERT INTO `data_source` VALUES ('3', 'patient', 'plate', '京A2125C');
INSERT INTO `data_source` VALUES ('1', 'sequence1', 'rote', '18.54');
INSERT INTO `data_source` VALUES ('2', 'sequence1', 'rote', '26.64');
INSERT INTO `data_source` VALUES ('1', 'sequence', 'sex', '18');
INSERT INTO `data_source` VALUES ('2', 'sequence', 'sex', '2');
INSERT INTO `data_source` VALUES ('1', 'sequence1', 'unit', '头');
INSERT INTO `data_source` VALUES ('2', 'sequence1', 'unit', '合');
INSERT INTO `data_source` VALUES ('1', 'sequence1', 'vg', '1');
INSERT INTO `data_source` VALUES ('2', 'sequence1', 'vg', '2');

新建表

CREATE TABLE `patient`  (
  `Id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

插入数据

INSERT INTO `patient` VALUES ('1', '张三');
INSERT INTO `patient` VALUES ('2', '李四');
INSERT INTO `patient` VALUES ('3', '王五');
INSERT INTO `patient` VALUES ('4', '李力');
INSERT INTO `patient` VALUES ('5', '王达');
INSERT INTO `patient` VALUES ('6', '名扬');
INSERT INTO `patient` VALUES ('7', '宋交明');
INSERT INTO `patient` VALUES ('8', '王文浩');

普通查询

SELECT a.*,c.`data` FROM patient AS a
INNER JOIN
(
SELECT
    b.`Id`,
        CONCAT('{',GROUP_CONCAT("\"",b.`key`,"\":\"",b.`Value`,"\""),'}') AS `data`
FROM data_source AS b WHERE b.TableName ='patient' GROUP BY b.Id ) AS c
ON a.Id=c.Id;

 

 带条件查询

SELECT a.id AS Id,a.`data` AS `Data`,b.`value` AS `Value` FROM
(
SELECT
    b.`Id`,
    CONCAT('{',GROUP_CONCAT("\"",b.`key`,"\":\"",b.`Value`,"\""),'}') AS `data`
FROM data_source AS b WHERE b.TableName ='sequence' AND b.Id IN(SELECT Id FROM  data_source WHERE  `Key` = 'age' AND `Value` = '18') GROUP BY b.Id) AS a
INNER JOIN sequence AS b on a.Id=b.`Name`;