msyql 行转列,字符串根据相应字符拆分多行

诚实可靠的悟空

已于 2022-07-19 16:49:07 修改

阅读量449

点赞数

分类专栏： mysql 文章标签： mysql sql 数据库

于 2020-08-12 08:49:18 首次发布

本文链接：https://blog.csdn.net/qq_20519453/article/details/107949045

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

需要用到 mysql库底下的 help_topic表辅助

SELECT
    substring_index(substring_index(    a.chain,'_',b.help_topic_id + 1    ),    '_' ,- 1    ) AS ID
FROM
	-- 替换为自己的表, 上下字段改一下就行
    (select '1_11_1223_1242' as chain) a  
JOIN mysql.help_topic b ON b.help_topic_id <
(length(a.chain) - length( replace(a.chain, '_', '')  ) + 1)

SELECT
    substring_index(substring_index(    a.dev_id,',',b.help_topic_id + 1    ),    ',' ,- 1    ) AS ID,a.*
FROM
    (SELECT
	t_sync_project.prj_name AS '项目名称',
	t_sync_project.prj_local_addr AS '项目地址',
	t_sync_project.prj_status as '项目状态',
	t_sync_device_remove.remove_declare_date AS '申报时间',
	t_sync_device_remove.remove_describe as '拆机备注',
	t_sync_device_remove.agree_remove_date as '同意拆除时间',
	t_sync_device_remove.plan_remove_date AS '计划拆除时间',
	t_sync_device_remove.actual_remove_date as '实际拆除时间',
	t_sync_device_remove.`status` as '进度',
	CASE 
	WHEN t_sync_device_remove.service_operator = 1 THEN	'电信'
	WHEN t_sync_device_remove.service_operator = 2 THEN '移动'
	WHEN t_sync_device_remove.service_operator = 3 THEN '联通'
	ELSE ''
END 
as '运营商',
	t_sync_project.prj_belong_addr as '区域',
	t_sync_device_remove.dc_pk as '工单号',
	t_sync_device_remove.dev_id as dev_id
FROM
	t_sync_device_remove
	LEFT JOIN t_sync_project ON t_sync_project.pro_id = t_sync_device_remove.pro_id) a  
JOIN mysql.help_topic b ON b.help_topic_id <
(length(a.dev_id) - length( replace(a.dev_id, ',', '')  ) + 1)

CREATE TABLE `t_sync_device_remove` (
  `dc_pk` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT '互联互通数据主键',
  `prj_name` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目名称',
  `province_prj_num` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目省代码',
  `pro_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '监管系统项目主键',
  `detect_pro_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '远程监控系统项目主键',
  `service_operator` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '服务运营商(取值为1:电信 2:移动,3:联通)',
  `corp_name` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '施工单位名称',
  `corp_code` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '施工单位组织机构代码',
  `dev_id` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '设备编号',
  `remove_declare_date` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拆除申报时间',
  `remove_remark` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拆除备注',
  `agree_remove_date` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '审核通过时间',
  `plan_remove_date` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '计划拆除完成时间(审核通过时间+10天)',
  `actual_remove_date` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '实际拆除完成时间',
  `remove_describe` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拆除说明',
  `corp_addr` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '施工单位联系地址',
  `pro_memo` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工程进展概况说明',
  `operator_file_url` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '运营商返回文件url',
  `file_url` varchar(512) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '监管系统上传文件url',
  `valid` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否作废(1：表示不作废0：表示作废)',
  `invalid_reason` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '作废理由',
  `invalid_date` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '作废时间',
  `source_id` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '源系统数据主键',
  `update_time` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据更新时间',
  `status` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据状态(1:新增，2:更新，4：删除)',
  `data_version` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据版本',
  `data_source` tinyint(2) DEFAULT NULL COMMENT '数据来源(0:互联互通,1:电信,2:移动,3:联通)',
  `sync_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '同步时间',
  `is_dirty` tinyint(1) DEFAULT '0' COMMENT '是否脏数据(1:是,0:否)',
  `hc_operate_state` varchar(8) CHARACTER SET tis620 COLLATE tis620_thai_ci DEFAULT NULL COMMENT '汇川操作状态（10：申请；20：受理；30：完成；40：作废）',
  PRIMARY KEY (`dc_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='设备删除';


CREATE TABLE `t_sync_project` (
  `dc_pk` varchar(63) COLLATE utf8mb4_general_ci NOT NULL COMMENT '互联互通数据主键',
  `pro_id` varchar(63) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目编号',
  `prj_num` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目编号',
  `prj_name` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目名称',
  `province_prj_num` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目省代码',
  `corp_code` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建设单位机构代码',
  `corp_name` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建设单位机构名称',
  `prj_belong_addr` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工程所属地',
  `prj_local_addr` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工程所在地',
  `build_corp_name` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建设单位名称',
  `build_corp_code` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建设单位组织机构代码',
  `build_person_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建设单位负责人',
  `build_person_phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建设单位负责人电话',
  `province_num` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目所在省',
  `city_num` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目所在地市',
  `county_num` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目所在区县',
  `prj_status` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '当前工程状态',
  `create_date` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '项目创建日期',
  `source_id` varchar(63) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '源系统数据主键',
  `update_time` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据更新时间',
  `status` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据状态(1:新增，2:更新，4：删除)',
  `data_version` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据版本',
  `data_source` tinyint(2) DEFAULT NULL COMMENT '数据来源(0:互联互通,1:电信,2:移动,3:联通)',
  `sync_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '同步时间',
  `is_dirty` tinyint(1) DEFAULT '0' COMMENT '是否脏数据(1:是,0:否)',
  PRIMARY KEY (`dc_pk`),
  KEY `idx_prj_name` (`prj_name`),
  KEY `idx_province_prj_num` (`province_prj_num`),
  KEY `idx_pro_id` (`pro_id`),
  KEY `idx_data_source` (`data_source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='项目工程';

INSERT INTO `hms_external`.`t_sync_device_remove` (`dc_pk`, `prj_name`, `province_prj_num`, `pro_id`, `detect_pro_id`, `service_operator`, `corp_name`, `corp_code`, `dev_id`, `remove_declare_date`, `remove_remark`, `agree_remove_date`, `plan_remove_date`, `actual_remove_date`, `remove_describe`, `corp_addr`, `pro_memo`, `operator_file_url`, `file_url`, `valid`, `invalid_reason`, `invalid_date`, `source_id`, `update_time`, `status`, `data_version`, `data_source`, `sync_time`, `is_dirty`, `hc_operate_state`) VALUES ('DCPK_2c9bc588731e954501732d8aadd47885', '漳州高新区圆山新城中部组团（路边安置房）棚户区改造项目（一期）', 'MA324TUKX201901001', 'f59b0785670d26960167262ea4ed092e', NULL, '3', '中铁二十二局集团第三工程有限公司', '155000419', '1ed21a68-059b-44e5-8870-932d06b1e06f,59e56f13-635c-49be-8e0b-c1eeadee76aa,e8e51e94-8dea-46f2-b2dd-7d556e669bc3,14a36837-21b1-4d73-85de-d6a39ca88b62,e734f66f-5b7f-4bd2-9437-b56179959f3b,e8f41706-9f21-481d-92d3-e044b3ebb271,94e51f2e-c220-40ea-a62e-44fd452d3d74,058269b7-e552-494b-b638-eba4e22a2ff7', '2020-05-22 00:00:00', '2#、3#、4#、5#塔吊拆卸告知已办理，塔吊已符合拆除条件，申请拆除2#（1065287、1065288）、3#（106293、106294）、4#（1065296、105297）、5#（1065299、105300）塔吊远程监控', '2020-07-06 16:53:53', '2020-07-16 16:53:53', '2020-04-28 14:18:18', '拆除完成', NULL, NULL, '', 'http://220.160.53.49:8888/default/com.hymake.common.secSeal.SecSealViewNoLogin.flow?eosFlowAction=secSealView&tableName=hy_video_remove&recordId=2c908a7b71f3107b017201e0994e4c79&code=devRemove001', '', NULL, NULL, NULL, '2020-07-15 08:05:10', '2', '1.03', '3', '2021-10-27 19:30:16', '0', NULL);

INSERT INTO `hms_external`.`t_sync_project` (`dc_pk`, `pro_id`, `prj_num`, `prj_name`, `province_prj_num`, `corp_code`, `corp_name`, `prj_belong_addr`, `prj_local_addr`, `build_corp_name`, `build_corp_code`, `build_person_name`, `build_person_phone`, `province_num`, `city_num`, `county_num`, `prj_status`, `create_date`, `source_id`, `update_time`, `status`, `data_version`, `data_source`, `sync_time`, `is_dirty`) VALUES ('DCPK_402881b8680d1495016833ebf2e33481', 'f59b0785670d26960167262ea4ed092e', '3506951901080101', '漳州高新区圆山新城中部组团（路边安置房）棚户区改造项目（一期）', 'MA324TUKX201901001', NULL, NULL, '漳州高新技术产业开发区', '漳州市高新区琥珀路以南、纵八路以东', '漳州铁发置业有限公司', 'MA324TUKX', '郑冠男', '13959552323', '350000', '350600', '350695', '竣工', '2019-01-08 00:00:00', '11DA5DB962AB4CD78F5D09E87562FA24', '2021-09-24 03:02:13', '2', '2.419999999999992', '3', '2021-10-27 19:07:50', '0');