创建数据
创建表
CREATE TABLE `aml_inst_risk_assess_category`
(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`dept_id` int(11) DEFAULT NULL COMMENT '机构id',
`dept_name` varchar(30) DEFAULT NULL COMMENT '机构名称',
`is_legal_person` varchar(10) DEFAULT NULL COMMENT '是否法人机构',
`supervision_dept_id` varchar(10) DEFAULT NULL COMMENT '监管机构id',
`supervision_dept_name` varchar(30) DEFAULT NULL COMMENT '监管机构名称',
`assessment_category` varchar(20) DEFAULT NULL COMMENT '评估类别',
`assessment_score` decimal(5, 2) DEFAULT NULL COMMENT '评估得分',
`assessment_date` date DEFAULT NULL COMMENT '评估日期',
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) DEFAULT '' COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 18364
DEFAULT CHARSET = utf8
ROW_FORMAT = DYNAMIC COMMENT ='机构风险评级类别表';
添加表数据
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (1, 25, '宁夏银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '基础信息', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (2, 25, '宁夏银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '内控机制建设', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (3, 25, '宁夏银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '日常监管情况', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (4, 25, '宁夏银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '风险评估', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (5, 25, '宁夏银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '风险自评估', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (6, 25, '宁夏银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '反洗钱监测', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (124, 26, '石嘴山银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '基础信息', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (125, 26, '石嘴山银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '内控机制建设', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
INSERT INTO aml_yc.aml_inst_risk_assess_category (id, dept_id, dept_name, is_legal_person, supervision_dept_id, supervision_dept_name, assessment_category, assessment_score, assessment_date, create_by, create_time, update_by, update_time, remark) VALUES (126, 26, '石嘴山银行股份有限公司', 'Y', '2', '中国人民银行宁夏回族自治区分行', '日常监管情况', 0.00, '2024-06-11', '1', '2024-06-11 17:21:46', null, null, null);
具体执行SQL
第一种方式(默认递增)
SELECT id,
(@rn := @rn + 1) AS row_number
FROM
aml_inst_risk_assess_category,
(SELECT @rn := 0) AS init_variable
ORDER BY id;
第二种方式(根据相同的值进行动态排序)
SET @row_number = 0;
SET @prev_value = NULL;
SELECT
dept_id,
IF(@prev_value = dept_id, @row_number, @row_number := @row_number + 1) AS same_value_rank,
@prev_value := dept_id,
dept_name
FROM
aml_inst_risk_assess_category
ORDER BY
dept_id;
第三种方式(在上边的基础上去掉 SET, 满足在mybatis可以直接使用)
SELECT
dept_id,
IF(@prev_value = dept_id, @row_number, @row_number := @row_number + 1) AS same_value_rank,
@prev_value := dept_id,
dept_name
FROM
aml_inst_risk_assess_category,
(select @row_number := 0,@prev_value := NULL) init_variable
ORDER BY
dept_id;