没有废话-MySQL,MyBatis 动态参数添加序号值(默认递增或根据内容进行递增)

创建数据

创建表

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;