使用日期作为列名的透视MySQL表

afdcj2ne 于 2023-02-03 发布在 Mysql

关注(0)|答案(1)|浏览(133)

如何才能获得此表x1c 0d1x
看起来像这样像这样。

下面是表定义和示例数据。

CREATE TABLE `result`  (
      `universal_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `flag` varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `measurement` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `units` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `reference_range` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `value_conv` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `unit_conv` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `ref_conv` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `test_code` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `order_no` int(11) NULL DEFAULT NULL,
      `sample_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `test_group` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `date` datetime(0) NULL DEFAULT NULL,
      `patient_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `status` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `instrument` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `his_code` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `his_mainid` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `section` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `sub_section` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
      `print_status` int(1) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `SampleID`(`sample_id`) USING BTREE,
      INDEX `Section`(`section`) USING BTREE,
      INDEX `SubSection`(`sub_section`) USING BTREE,
      INDEX `TestCode`(`test_code`) USING BTREE,
      INDEX `Machine`(`instrument`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 463617 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

INSERT INTO `result` VALUES ('White Blood Cells', '', '7.99', 'x10^9/L', '4.00-10.00', '8.0', 'x10^9/L', '4.0-10.0', 'WBC', 107649, 4, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Red Blood Cells', '', '5.24', 'x10^12/L', '3.50-5.50', '5.24', 'x10^12/L', '3.50-5.50', 'RBC', 107650, 3, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Hemoglobin', '', '14.1', 'g/L', '11.00-15.00', '14.1', 'g/L', '110-150', 'HGB', 107651, 1, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Hematocrit', '', '0.41', 'vol%', '0.37-0.47', '0.41', 'vol%', '0.37-0.47', 'HCT', 107652, 2, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('MCV', 'L', '78.1', 'fL', '80.00-96.00', '78.1', 'fL', '80-96', 'MCV', 107653, 11, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('MCH', 'L', '26.9', 'pg', '27.00-33.00', '26.9', 'pg', '27-33', 'MCH', 107654, 12, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('MCHC', '', '34.5', 'g/L', '33.40-35.50', '34.5', 'g/L', '33-36', 'MCHC', 107655, 13, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Platelet Count', '', '224,000', 'x10^3/uL', '100000.00-300000.00', '224', 'x10^3/uL', '100,000-300,000', 'PLT', 107656, 5, '0006261', 'Complete Blood Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Neutrophils', '', '0.58', '', '0.50-0.70', '0.6', '', '1-1', 'NEU_P', 107657, 6, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Lymphocytes', '', '0.36', '', '0.20-0.40', '0.4', '', '0-0', 'LYM_P', 107658, 9, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Monocytes', '', '0.06', '', '0.03-0.12', '0.1', '', '0-0', 'MON_P', 107659, 10, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Eosinophils', 'L', '0.00', '', '0.01-0.05', '0.0', '', '0-0', 'EOS_P', 107660, 7, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);
INSERT INTO `result` VALUES ('Basophils', '', '0.00', '', '0.00-0.01', '0.0', '', '0-0', 'BAS_P', 107661, 8, '0006261', 'Differential Count', '2021-04-14 05:22:47', '271489', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC', 1);

INSERT INTO `result` VALUES ('White Blood Cells', '', '28.04', 'x10^9/L', '', '28.0', 'x10^9/L', '', 'WBC', 107664, 4, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Red Blood Cells', '', '5.18', 'x10^12/L', '', '5.18', 'x10^12/L', '', 'RBC', 107665, 3, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Hemoglobin', '', '17.7', 'g/L', '', '17.7', 'g/L', '', 'HGB', 107666, 1, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Hematocrit', '', '0.50', 'vol%', '', '0.50', 'vol%', '', 'HCT', 107667, 2, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('MCV', '', '96.1', 'fL', '', '96.1', 'fL', '', 'MCV', 107668, 11, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('MCH', '', '34.2', 'pg', '', '34.2', 'pg', '', 'MCH', 107669, 12, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('MCHC', '', '35.5', 'g/L', '', '35.5', 'g/L', '', 'MCHC', 107670, 13, '0006261', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Platelet Count', '', '258,000', 'x10^3/uL', '', '258', 'x10^3/uL', '', 'PLT', 107671, 5, '0006265', 'Complete Blood Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Neutrophils', '', '0.80', '', '', '0.8', '', '', 'NEU_P', 107672, 6, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Lymphocytes', '', '0.13', '', '', '0.1', '', '', 'LYM_P', 107673, 9, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Monocytes', '', '0.06', '', '', '0.1', '', '', 'MON_P', 107674, 10, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Eosinophils', '', '.01', '', '', '0.0', '', '', 'EOS_P', 107675, 7, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);
INSERT INTO `result` VALUES ('Basophils', '', '0.00', '', '', '0.0', '', '', 'BAS_P', 107676, 8, '0006261', 'Differential Count', '2021-04-14 05:24:18', '271472', NULL, 'Other_Test', NULL, NULL, 'Hematology', 'CBC+BType', 1);

这是我使用的查询。

SET SESSION group_concat_max_len = 1000000;

SELECT
    GROUP_CONCAT(DISTINCT
        CAST(CONCAT('IFNULL((CASE WHEN `date` = ''', `date`, ''' THEN `measurement` END), NULL) AS ', `date`, '') AS CHAR)
     )INTO @sql
FROM
    `result` ORDER BY `date` DESC;
    
SET @sql = CONCAT('SELECT patient_id, universal_id AS `Parameter`, ', CAST(@sql as CHAR), ' 
                                    FROM result 
                                     GROUP BY universal_id ORDER BY order_no');
                                     
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

谢谢大家！

mysql

来源：https://stackoverflow.com/questions/71480990/pivot-mysql-table-using-date-as-column-name

1条答案

按热度按时间

zqry0prt1#

经过审查的链接，然后在评论中发送一些调整，我有它的工作。这里的代码，我用于未来的参考。

CREATE DEFINER=`root`@`localhost` PROCEDURE `delta_check`(IN PID VARCHAR(50), IN Section VARCHAR(50), IN SubSection VARCHAR(50))
BEGIN
    SET @PID = PID;
    SET @Section = Section;
    SET @SubSection = SubSection;
    
    SET SESSION group_concat_max_len = 1000000;
    
    SELECT GROUP_CONCAT(DISTINCT CAST(CONCAT("MAX(CASE WHEN DATE_FORMAT(`date`, '%y-%m-%d %H:%i') = '", DATE_FORMAT(`date`, '%y-%m-%d %H:%i'), "' THEN `measurement` END) AS `", DATE_FORMAT(`date`, '%y-%M-%d %H:%i'), "`") AS CHAR)) FROM result WHERE `patient_id` = PID AND section = Section AND sub_section = SubSection INTO @sql;
    
    SET @sql = CONCAT("SELECT result.universal_id AS Parameter, specimen.si_unit AS Unit, reference_range.si_range, result.test_code AS `TestCode`, ", @sql, ", result.`section` AS Section, result.`sub_section` AS SubSection
                                        FROM result 
                                        LEFT JOIN specimen ON result.test_code = specimen.test_code
                                        LEFT JOIN reference_range ON result.test_code = reference_range.test_code
                                        WHERE result.`patient_id` = ? AND result.section = ? AND result.sub_section = ? GROUP BY result.test_code ORDER BY specimen.order_no ASC");

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @PID, @Section, @SubSection;
    DEALLOCATE PREPARE stmt;
END

为你们的帮助干杯。
谢谢大家！

赞(0）回复(0）举报 2023-02-03

我来回答

使用日期作为列名的透视MySQL表

1条答案

相关问题

热门标签

最新问答