mysql 列转行

最新推荐文章于 2024-06-13 08:37:22 发布

指尖‖舞者

最新推荐文章于 2024-06-13 08:37:22 发布

阅读量77

点赞数 3

文章标签： java

本文链接：https://blog.csdn.net/weixin_39472101/article/details/138239471

版权

CREATE TABLE `process_demo` (  
    `process_id` VARCHAR(255) NOT NULL, -- 假设process_id是变长字符串类型  
    `porcess_type` INT(11) NOT NULL,    -- 假设porcess_type是整数类型  
    `process_name` VARCHAR(255) NOT NULL, -- 假设process_name是变长字符串类型  
    `operator` VARCHAR(50) NOT NULL,      -- 假设operator是变长字符串类型，存储操作者的标识或名称  
    `staff_name` VARCHAR(50) NOT NULL,    -- 假设staff_name是变长字符串类型，存储员工姓名  
    `process_after_status` VARCHAR(50) NOT NULL, -- 假设process_after_status是变长字符串类型，存储处理后的状态  
    `record_time` DATETIME NOT NULL,       -- 假设record_time是日期时间类型，存储记录时间  
    `result` VARCHAR(255) NOT NULL,        -- 假设result是变长字符串类型，存储结果描述  
    PRIMARY KEY (`process_id`) -- 假设process_id是主键  
);

INSERT INTO `process_demo` (`process_id`, `porcess_type`, `process_name`, `operator`, `staff_name`, `process_after_status`, `record_time`, `result`) VALUES ('0125179e6ea8492eae239fd2b2ebe65d', '4', '专科护士认证申请', '19*1', '马*飞', 'nurse_4_apply', '2024-04-24 16:19:22', '发起专科护士认证申请');



INSERT INTO `process_demo` (`process_id`, `porcess_type`, `process_name`, `operator`, `staff_name`, `process_after_status`, `record_time`, `result`) VALUES ('0125179e6ea8492eae239fd2b2ebe65d', '4', '专科护士认证申请', '8*21', '胡*', '3_process_agree', '2024-04-24 16:34:47', '组长审批通过');

INSERT INTO `process_demo` (`process_id`, `porcess_type`, `process_name`, `operator`, `staff_name`, `process_after_status`, `record_time`, `result`) VALUES ('0125179e6ea8492eae239fd2b2ebe65d', '4', '专科护士认证申请', '65*4', '汪未*', '4_process_agree', '2024-04-24 16:44:45', '医疗护士长审批通过');

INSERT INTO `process_demo` (`process_id`, `porcess_type`, `process_name`, `operator`, `staff_name`, `process_after_status`, `record_time`, `result`) VALUES ('0125179e6ea8492eae239fd2b2ebe65d', '4', '专科护士认证申请', '6*81', '程*', '5_process_agree', '2024-04-24 16:56:47', '总护士长审批通过');

SELECT   
    process_id,  
    porcess_type,  
    MAX(CASE WHEN process_after_status = 'nurse_4_apply' THEN operator ELSE NULL END) AS operator_nurse_4_apply,  
    MAX(CASE WHEN process_after_status = 'nurse_4_apply' THEN staff_name ELSE NULL END) AS staff_name_nurse_4_apply,  
    MAX(CASE WHEN process_after_status = 'nurse_4_apply' THEN record_time ELSE NULL END) AS record_time_nurse_4_apply,  
    MAX(CASE WHEN process_after_status = 'nurse_4_apply' THEN result ELSE NULL END) AS result_nurse_4_apply,  
    MAX(CASE WHEN process_after_status = '3_process_agree' THEN operator ELSE NULL END) AS operator_3_process_agree,  
    MAX(CASE WHEN process_after_status = '3_process_agree' THEN staff_name ELSE NULL END) AS staff_name_3_process_agree,  
    MAX(CASE WHEN process_after_status = '3_process_agree' THEN record_time ELSE NULL END) AS record_time_3_process_agree,  
    MAX(CASE WHEN process_after_status = '3_process_agree' THEN result ELSE NULL END) AS result_3_process_agree
FROM   
    process_demo  
WHERE   
    process_id = '0125179e6ea8492eae239fd2b2ebe65d' AND   
    porcess_type = '4'  
GROUP BY   
    process_id, porcess_type;