有個(gè)需求：將總分表，前25%的人員，設(shè)置為AAA，中間55%設(shè)置為AA，后20%設(shè)置為A

表結(jié)構(gòu)如下：

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 '主體評(píng)價(jià)分',
  `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 '信用等級(jí)',
  `CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `UPDATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '更新時(shí)間',
  `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è)績(jī)分',
  `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 = '評(píng)分結(jié)果匯總表' ROW_FORMAT = Dynamic;

實(shí)現(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
)