有个需求:将总分表,前25%的人员,设置为AAA,中间55%设置为AA,后20%设置为A
表结构如下:
CREATE TABLE `libra_score_summary` (
`ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '主键',
`PERSON_ID` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '人员ID',
`BASIC_SCORE` decimal(32, 2) NULL DEFAULT NULL COMMENT '基本信息分',
`SUBJECT_SCORE` decimal(32, 2) NULL DEFAULT NULL COMMENT '主体评价分',
`GOOD_SCORE` decimal(32, 2) NULL DEFAULT NULL COMMENT '良好信息加分',
`BAD_SCORE` decimal(32, 2) NULL DEFAULT NULL COMMENT '不良信息扣分',
`TOTAL_SCORE` decimal(32, 2) NULL DEFAULT NULL COMMENT '总分',
`CREDIT_RAITING` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '信用等级',
`CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`UPDATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
`CREDIT_RAITING_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '信用码[1:绿码 0:黄码 -1:红码]',
`PERFORMANCE_SCORE` double NULL DEFAULT NULL COMMENT '业绩分',
`ROLE_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '评分结果汇总表' ROW_FORMAT = Dynamic;
实现语句
-- 更新前25的人为AAA
先算出来前25%:select ROUND(count(1)*0.25) from libra_score_summary
UPDATE libra_score_summary
SET CREDIT_RAITING = 'AAA'
WHERE id IN (
SELECT id
FROM (
SELECT id,TOTAL_SCORE
FROM libra_score_summary
ORDER BY TOTAL_SCORE DESC
LIMIT 上边查询的数值
) t
)