有個需求：將總分表伊约，前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 '創(chuàng)建時間',
`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 '業(yè)績分',
`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;
實現(xiàn)語句
-- 更新前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 上邊查詢的數(shù)值
) t
)
