测试数据

mysql列转行

create  table testTable( 
 id int identity primary key auto_increment,
 name varchar(20),         
 subject  varchar(20),
 score dec (18,2)
);

insert into testTable (name,subject,score) values ('张三','语文',41);
insert into testTable (name,subject,score) values ('张三','英语',94);
insert into testTable (name,subject,score) values ('张三','数学',89);
insert into testTable (name,subject,score) values ('张三','物理',96);
insert into testTable (name,subject,score) values ('张三','生物',90);

insert into testTable (name,subject,score) values ('李四','语文',63);
insert into testTable (name,subject,score) values ('李四','英语',81);
insert into testTable (name,subject,score) values ('李四','数学',82);
insert into testTable (name,subject,score) values ('李四','物理',52);
insert into testTable (name,subject,score) values ('李四','生物',82);

insert into testTable (name,subject,score) values ('王五','语文',98);
insert into testTable (name,subject,score) values ('王五','英语',99);
insert into testTable (name,subject,score) values ('王五','数学',97);
insert into testTable (name,subject,score) values ('王五','物理',97);
insert into testTable (name,subject,score) values ('王五','生物',91);

insert into testTable (name,subject,score) values ('赵四','语文',98);
insert into testTable (name,subject,score) values ('赵四','英语',98);
insert into testTable (name,subject,score) values ('赵四','数学',97);
insert into testTable (name,subject,score) values ('赵四','物理',96);
insert into testTable (name,subject,score) values ('赵四','生物',95);

insert into testTable (name,subject,score) values ('李芳','语文',80);
insert into testTable (name,subject,score) values ('李芳','英语',65);
insert into testTable (name,subject,score) values ('李芳','数学',85);
insert into testTable (name,subject,score) values ('李芳','物理',56);
insert into testTable (name,subject,score) values ('李芳','生物',88);

insert into testTable (name,subject,score) values ('周杰','语文',50);
insert into testTable (name,subject,score) values ('周杰','英语',95);
insert into testTable (name,subject,score) values ('周杰','数学',85);
insert into testTable (name,subject,score) values ('周杰','物理',89);
insert into testTable (name,subject,score) values ('周杰','生物',80)

静态写法：

SELECT `name`
    ,
    MAX( CASE WHEN `subject` = '语文' THEN score END ) AS '语文' ,
    MAX( CASE WHEN `subject` = '英语' THEN score END ) AS '英语' ,
    MAX( CASE WHEN `subject` = '数学' THEN score END ) AS '数学' ,
    MAX( CASE WHEN `subject` = '物理' THEN score END ) AS '物理' ,
    MAX( CASE WHEN `subject` = '生物' THEN score END ) AS '生物' 
FROM
    testtable 
GROUP BY
    `name`;

 

动态写法：

SET @SQL = NULL;-- 拼接
SELECT
    GROUP_CONCAT(
    DISTINCT CONCAT( 'sum(case subject', ' when ''', SUBJECT, ''' then score else 0 end) as ''', SUBJECT, '''' )) INTO @SQL 
FROM
    testtable;-- 查看拼接结果
SELECT
    @SQL; -- sum(case subject when '数学' then score else 0 end) as '数学',sum(case subject when '物理' then score else 0 end) as '物理',sum(case subject when '生物' then score else 0 end) as '生物',sum(case subject when '英语' then score else 0 end) as '英语',sum(case subject when '语文' then score else 0 end) as '语文'

SET @SQL = CONCAT( 'select b.name,', @SQL, 'from testtable b GROUP BY name' );

-- select b.name,sum(case subject when '数学' then score else 0 end) as '数学',sum(case subject when '物理' then score else 0 end) as '物理',sum(case subject when '生物' then score else 0 end) as '生物',sum(case subject when '英语' then score else 0 end) as '英语',sum(case subject when '语文' then score else 0 end) as '语文'from testtable b GROUP BY name
SELECT
    @SQL;-- 使用预执行 PREPARE 声明 stmt
PREPARE stmt 
FROM
    @SQL;-- 开始执行
EXECUTE stmt;-- 清除
DEALLOCATE PREPARE stmt;

 

 

例子2：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80012
 Source Host           : localhost:3306
 Source Schema         : supervision

 Target Server Type    : MySQL
 Target Server Version : 80012
 File Encoding         : 65001

 Date: 19/01/2021 18:01:40
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sv_check_attachment
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_attachment`;
CREATE TABLE `sv_check_attachment`  (
  `ATTACHMENT_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '附件ID',
  `RESULT_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '检查结果ID',
  `ATT_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件名称',
  `ATT_DES` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件描述',
  `ATT_TYPE` int(11) NULL DEFAULT NULL COMMENT '附件类型',
  `ATT_URL` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件路径',
  `ATT_SIZE` decimal(17, 2) NULL DEFAULT NULL COMMENT '附件大小',
  `ATT_USER` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上传人',
  `ATT_TIME` datetime(0) NULL DEFAULT NULL COMMENT '上传时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '检查附件' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sv_check_form_sub_template
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_form_sub_template`;
CREATE TABLE `sv_check_form_sub_template`  (
  `SUB_TEMPLATE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '子模板ID',
  `SUB_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `SUB_TYPE` int(11) NULL DEFAULT NULL COMMENT '模板类别',
  `SUB_ORDER` int(11) NULL DEFAULT NULL COMMENT '排序',
  `IS_VALID` int(11) NULL DEFAULT NULL COMMENT '是否有效',
  `IS_SHOW` int(11) NULL DEFAULT NULL COMMENT '是否在页面显示',
  `DIS_TYPE` int(11) NULL DEFAULT NULL COMMENT '展现类型',
  `SUB_STATE` int(11) NULL DEFAULT NULL COMMENT '模板状态',
  `SUB_CODE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '编号',
  `SUB_LEVEL` int(11) NULL DEFAULT NULL COMMENT '级别',
  `SUB_PARENT_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父级ID',
  `SUB_IS_KEY_POINT` int(11) NULL DEFAULT NULL COMMENT '是否重点项',
  `ORGANIZATION_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属单位',
  `CREATE_USER_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_USER` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `UPDATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `USER_DISABLED` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '失效人',
  `DISABLED_TIME` datetime(0) NULL DEFAULT NULL COMMENT '失效时间',
  PRIMARY KEY (`SUB_TEMPLATE_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '检查单子模板' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_check_form_sub_template
-- ----------------------------
INSERT INTO `sv_check_form_sub_template` VALUES ('1', '登记模板1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', '1', NULL, '1', NULL, '1', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('10', '登记模板10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '10', '10', NULL, '10', NULL, '10', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('11', '冷库清单', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, '1', '1', NULL, '1', NULL, '1', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('12', '冷库清单1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '11', NULL, '3', '3', NULL, '3', NULL, '3', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('13', '冷库名称2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '11', NULL, '4', '4', NULL, '4', NULL, '4', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('14', '冷库名称3', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '11', NULL, '5', '5', NULL, '5', NULL, '5', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('15', '登记模板6', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '6', '6', NULL, '6', NULL, '6', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('16', '登记模板7', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '7', NULL, '7', NULL, '7', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('17', '登记模板8', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '8', '8', NULL, '8', NULL, '8', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('18', '登记模板9', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '9', '9', NULL, '9', NULL, '9', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('19', '登记模板10', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '10', '10', NULL, '10', NULL, '10', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('2', '登记模板2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2', '2', NULL, '2', NULL, '2', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('20', '检查结果模板', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('3', '登记模板3', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '3', '3', NULL, '3', NULL, '3', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('4', '登记模板4', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '4', '4', NULL, '4', NULL, '4', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('5', '登记模板5', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '5', '5', NULL, '5', NULL, '5', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('6', '登记模板6', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '6', '6', NULL, '6', NULL, '6', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('7', '登记模板7', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '7', NULL, '7', NULL, '7', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('8', '登记模板8', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '8', '8', NULL, '8', NULL, '8', NULL);
INSERT INTO `sv_check_form_sub_template` VALUES ('9', '登记模板9', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '9', '9', NULL, '9', NULL, '9', NULL);

-- ----------------------------
-- Table structure for sv_check_form_template
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_form_template`;
CREATE TABLE `sv_check_form_template`  (
  `TEMPLATE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '检查单模板ID',
  `NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '模板名称',
  `IS_VALID` int(11) NULL DEFAULT NULL COMMENT '是否有效',
  `TEMPLATE_STATE` int(11) NULL DEFAULT NULL COMMENT '模板状态',
  `ORGANIZATION_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属单位',
  `CREATE_USER_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_USER` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `UPDATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `USER_DISABLED` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '失效人',
  `DISABLED_TIME` datetime(0) NULL DEFAULT NULL COMMENT '失效时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '检查单模板' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for sv_check_obj_relation
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_obj_relation`;
CREATE TABLE `sv_check_obj_relation`  (
  `RELATION_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '对象关系ID',
  `RELATION_TYPE` int(11) NULL DEFAULT NULL COMMENT '关系类型',
  `OBJ1_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联对象1ID',
  `OBJ2_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联对象2ID',
  `SHOW_ORDER` int(11) NULL DEFAULT NULL COMMENT '显示顺序',
  `DEFAULT_VALUE` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '默认值',
  `ROW_NUM` int(11) NULL DEFAULT NULL COMMENT '行号'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '对象关系' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_check_obj_relation
-- ----------------------------
INSERT INTO `sv_check_obj_relation` VALUES ('1', 5, '1', '1', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('2', 5, '1', '2', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('3', 5, '1', '3', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('4', 5, '1', '4', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('5', 5, '1', '5', NULL, '3,4', NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('6', 5, '1', '6', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('7', 5, '1', '7', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('8', 5, '1', '8', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('9', 5, '1', '9', NULL, NULL, NULL);
INSERT INTO `sv_check_obj_relation` VALUES ('10', 5, '12', '21', 1, '冷库名称1', 1);
INSERT INTO `sv_check_obj_relation` VALUES ('11', 5, '12', '22', 2, '地址1', 1);
INSERT INTO `sv_check_obj_relation` VALUES ('12', 5, '12', '23', 3, '1', 1);
INSERT INTO `sv_check_obj_relation` VALUES ('13', 5, '12', '24', 4, '0', 1);
INSERT INTO `sv_check_obj_relation` VALUES ('14', 5, '12', '25', 5, '3,4,5', 1);
INSERT INTO `sv_check_obj_relation` VALUES ('15', 5, '12', '21', 1, '冷库名称2', 2);
INSERT INTO `sv_check_obj_relation` VALUES ('16', 5, '12', '22', 2, '地址2', 2);
INSERT INTO `sv_check_obj_relation` VALUES ('17', 5, '12', '23', 3, '1', 2);
INSERT INTO `sv_check_obj_relation` VALUES ('18', 5, '12', '24', 4, '0', 2);
INSERT INTO `sv_check_obj_relation` VALUES ('19', 5, '12', '25', 5, '3,4,5', 2);
INSERT INTO `sv_check_obj_relation` VALUES ('20', 5, '12', '26', 6, '检查结果值', 2);

-- ----------------------------
-- Table structure for sv_check_record
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_record`;
CREATE TABLE `sv_check_record`  (
  `RECORD_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '记录ID',
  `RESULT_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '检查结果ID',
  `SUB_TEMPLATE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '子模板ID',
  `INDICATOR_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '指标ID',
  `INDICATOR_VALUE` varchar(5000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '指标值',
  `INDICATOR_ROW` int(11) NULL DEFAULT NULL COMMENT '行号',
  PRIMARY KEY (`RECORD_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '记录信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_check_record
-- ----------------------------
INSERT INTO `sv_check_record` VALUES ('1', '1', '1', '检查项目描述1@@@检查项结果值@@@检查结论', '是否持有营业执照@@@合格@@@通过', 1);

-- ----------------------------
-- Table structure for sv_check_result
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_result`;
CREATE TABLE `sv_check_result`  (
  `RESULT_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '检查结果ID',
  `TEMPLATE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '检查单模板ID',
  `CHECK_SUBJECT_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '检查主体ID',
  `RESULT_TITLE` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '检查结果名称',
  `RESULT_DES` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '检查结果描述',
  `IS_VALID` int(11) NULL DEFAULT NULL COMMENT '是否有效',
  `ORGANIZATION_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属单位',
  `CREATE_USER_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_USER` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `UPDATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `USER_DISABLED` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '失效人',
  `DISABLED_TIME` datetime(0) NULL DEFAULT NULL COMMENT '失效时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '检查结果' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_check_result
-- ----------------------------
INSERT INTO `sv_check_result` VALUES ('1', '检查结果1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- ----------------------------
-- Table structure for sv_check_template_indicator
-- ----------------------------
DROP TABLE IF EXISTS `sv_check_template_indicator`;
CREATE TABLE `sv_check_template_indicator`  (
  `INDICATOR_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '指标ID',
  `FIELD_NAME` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字段名称',
  `IS_EDIT` int(11) NULL DEFAULT NULL COMMENT '是否可修改',
  `FIELD_VALUE` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字段值',
  `FIELD_TYPE` int(11) NULL DEFAULT NULL COMMENT '指标类型',
  `CODE_TYPE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '码表分类ID',
  `FIELD_GROUP` int(11) NULL DEFAULT NULL COMMENT '分组',
  `FIELD_ORDER` int(11) NULL DEFAULT NULL COMMENT '排序',
  `IS_ROW` int(11) NULL DEFAULT NULL COMMENT '是否独占一行',
  `IS_VALID` int(11) NULL DEFAULT NULL COMMENT '是否有效',
  `INDICATOR_STATE` int(11) NULL DEFAULT NULL COMMENT '指标状态',
  `ORGANIZATION_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所属单位',
  `CREATE_USER_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `UPDATE_USER` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人',
  `UPDATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `USER_DISABLED` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '失效人',
  `DISABLED_TIME` datetime(0) NULL DEFAULT NULL COMMENT '失效时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '模板指标' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_check_template_indicator
-- ----------------------------
INSERT INTO `sv_check_template_indicator` VALUES ('1', '企业注册资本', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('2', '企业名称', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('3', '地址', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('4', '电话', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('5', '单位类别', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('6', '检查人', NULL, NULL, NULL, '检查人员组织结构数据sql？', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('7', '检查人联系方式', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('8', '存在问题', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('9', '改进建议', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('11', '抽查事项编号', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('12', '检查内容', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('13', '有效性', NULL, NULL, NULL, 'valid_type', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('14', '检查结果显示值', NULL, NULL, NULL, 'check_indicator', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('15', '检查依据', NULL, '《公共场所卫生管理条例》', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('16', '检查方法', NULL, '实地检查，不定项检查或抽查', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('17', '常见问题', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('18', '计分标准', NULL, '100', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('19', '分数', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('21', '地址', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('22', '冷冻库还是冷藏库', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('23', '是否存储冷冻肉制品或水产品', NULL, NULL, NULL, 'is_or_not', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('24', '是否符合“三全”要求', NULL, NULL, NULL, 'is_or_not', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('25', '存放进口产品', NULL, NULL, NULL, 'is_imported_product', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sv_check_template_indicator` VALUES ('26', '检查结果值', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- ----------------------------
-- Table structure for sv_code_type
-- ----------------------------
DROP TABLE IF EXISTS `sv_code_type`;
CREATE TABLE `sv_code_type`  (
  `CODE_TYPE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '码表分类ID',
  `CODE_TYPE_NAME` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类名称',
  `CODE_TYPE_DES` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类描述',
  `IS_VALID` int(11) NULL DEFAULT NULL COMMENT '是否有效'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '码表分类' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_code_type
-- ----------------------------
INSERT INTO `sv_code_type` VALUES ('organization_type', '单位分类', NULL, 1);
INSERT INTO `sv_code_type` VALUES ('is_or_not', '是否', NULL, 1);
INSERT INTO `sv_code_type` VALUES ('is_imported_product', '存放进口产品', NULL, 1);
INSERT INTO `sv_code_type` VALUES ('valid_type', '有效性', NULL, 1);
INSERT INTO `sv_code_type` VALUES ('check_indicator', '检查指标值', NULL, 1);
INSERT INTO `sv_code_type` VALUES ('check_period', '检查频次', NULL, 1);

-- ----------------------------
-- Table structure for sv_code_value
-- ----------------------------
DROP TABLE IF EXISTS `sv_code_value`;
CREATE TABLE `sv_code_value`  (
  `CODE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '码表值ID',
  `CODE_TYPE_ID` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '码表分类ID',
  `CODE` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '代码CODE',
  `CODE_VALUE` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '代码值',
  `CODE_ORDER` int(11) NULL DEFAULT NULL COMMENT '排序',
  `IS_VALID` int(11) NULL DEFAULT NULL COMMENT '是否有效'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '码表值' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sv_code_value
-- ----------------------------
INSERT INTO `sv_code_value` VALUES ('1', 'organization_type', '1', '农贸市场', 1, 1);
INSERT INTO `sv_code_value` VALUES ('2', 'organization_type', '2', '水产销售单位', 2, 1);
INSERT INTO `sv_code_value` VALUES ('3', 'organization_type', '3', '餐饮单位', 3, 1);
INSERT INTO `sv_code_value` VALUES ('4', 'organization_type', '4', '餐具制售单位', 4, 1);
INSERT INTO `sv_code_value` VALUES ('5', 'valid_type', '1', '有效', 1, 1);
INSERT INTO `sv_code_value` VALUES ('6', 'valid_type', '2', '无效', 2, 1);
INSERT INTO `sv_code_value` VALUES ('7', 'check_indicator', '1', '合格', 1, 1);
INSERT INTO `sv_code_value` VALUES ('8', 'check_indicator', '2', '不合格', 2, 1);
INSERT INTO `sv_code_value` VALUES ('9', 'check_indicator', '3', '符合', 3, 1);
INSERT INTO `sv_code_value` VALUES ('10', 'check_indicator', '4', '不符合', 4, 1);
INSERT INTO `sv_code_value` VALUES ('11', 'check_indicator', '5', '其他', 5, 1);
INSERT INTO `sv_code_value` VALUES ('12', 'check_period', '1', '每年一次', 1, 1);
INSERT INTO `sv_code_value` VALUES ('13', 'check_period', '2', '每月一次', 2, 1);
INSERT INTO `sv_code_value` VALUES ('14', 'check_period', '3', '每周一次', 3, 1);
INSERT INTO `sv_code_value` VALUES ('15', 'is_or_not', '1', '是', 1, 1);
INSERT INTO `sv_code_value` VALUES ('16', 'is_or_not', '2', '否', 2, 1);
INSERT INTO `sv_code_value` VALUES ('17', 'is_imported_product', '1', '是', 1, 1);
INSERT INTO `sv_code_value` VALUES ('18', 'is_imported_product', '2', '否', 2, 1);
INSERT INTO `sv_code_value` VALUES ('19', 'is_imported_product', '3', '海（水）产', 3, 1);
INSERT INTO `sv_code_value` VALUES ('20', 'is_imported_product', '4', '水果', 4, 1);
INSERT INTO `sv_code_value` VALUES ('21', 'is_imported_product', '5', '乳制品', 5, 1);
INSERT INTO `sv_code_value` VALUES ('22', 'is_imported_product', '6', '调味品', 6, 1);
INSERT INTO `sv_code_value` VALUES ('23', 'is_imported_product', '7', '饮料', 7, 1);

-- ----------------------------
-- Procedure structure for 冷库清单
-- ----------------------------
DROP PROCEDURE IF EXISTS `冷库清单`;
delimiter ;;
CREATE PROCEDURE `冷库清单`()
BEGIN
  #Routine body goes here...
SET @sql=null;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE INDICATOR_ID WHEN ''',scti1.INDICATOR_ID,''' THEN DEFAULT_VALUE ELSE ''' ''' END)  AS ''',scti1.INDICATOR_ID,'''')) FROM sv_check_obj_relation scor1 LEFT JOIN sv_check_template_indicator scti1 ON scor1.OBJ2_ID=scti1.INDICATOR_ID WHERE scor1.OBJ1_ID=12 INTO @sql;
-- SELECT @sql;
SET @sql=CONCAT('select a.ROW_NUM,',@sql,' FROM (
SELECT svor.*,scti.INDICATOR_ID FROM sv_check_obj_relation svor LEFT JOIN sv_check_form_sub_template SCFST
 ON
  svor.OBJ1_ID=SCFST.SUB_TEMPLATE_ID
    LEFT JOIN sv_check_template_indicator scti on
    svor.OBJ2_ID=scti.INDICATOR_ID
WHERE OBJ1_ID=12 AND RELATION_TYPE=5
) a GROUP BY a.ROW_NUM');
-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
 

 

SET @sql=null;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE INDICATOR_ID WHEN ''',scti1.INDICATOR_ID,''' THEN DEFAULT_VALUE ELSE ''' ''' END)  AS ''',scti1.INDICATOR_ID,'''')) FROM sv_check_obj_relation scor1 LEFT JOIN sv_check_template_indicator scti1 ON scor1.OBJ2_ID=scti1.INDICATOR_ID WHERE scor1.OBJ1_ID=12 INTO @sql;
SELECT @sql;
SET @sql=CONCAT('select a.ROW_NUM,',@sql,' FROM (
SELECT svor.*,scti.INDICATOR_ID FROM sv_check_obj_relation svor LEFT JOIN sv_check_form_sub_template SCFST
 ON
  svor.OBJ1_ID=SCFST.SUB_TEMPLATE_ID
    LEFT JOIN sv_check_template_indicator scti on
    svor.OBJ2_ID=scti.INDICATOR_ID
WHERE OBJ1_ID=12 AND RELATION_TYPE=5
) a GROUP BY a.ROW_NUM');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;