CREATE TABLE test (kh VARCHAR(10), xm VARCHAR(10),cr DECIMAL(4,2), xf DECIMAL(4,2),ye DECIMAL(4,2))
 INSERT INTO test 
 SELECT '0001', '张三', 5.00, NULL, 3.00  
 UNION ALL 
 SELECT '0001', '张三', NULL, 1.00, 3.00  
 UNION ALL 
 SELECT '0001', '张三', NULL, 1.00 ,3.00  
 UNION ALL 
 SELECT '0002', '李四', 10.00, NULL ,30.00
 UNION ALL 
 SELECT '0002', '李四', 10.00, NULL ,30.00
 UNION ALL 
 SELECT '0002', '李四', 10.00, NULL ,30.00
 UNION ALL 
 SELECT '0003', '王五', 20.00, NULL, 0.00
 UNION ALL 
 SELECT '0003', '王五', NULL, 10.00, 0.00
 UNION ALL 
 SELECT '0003', '王五', NULL, 10.00 ,0.00
 
 
 
 SELECT ROW_NUMBER()OVER(PARTITION BY kh ORDER BY kh, xm ,cr DESC  )id ,* INTO t FROM test 
 
 UPDATE t
 SET ye = NULL 
 FROM t t
 WHERE  NOT  EXISTS ( SELECT 1 FROM (SELECT MAX (id) id ,kh FROM t GROUP BY kh) b WHERE t.id=b.id AND t.kh=b.kh)
 
 SELECT * FROM t
 
 /*
 id                   kh         xm         cr                                      xf                                      ye
 -------------------- ---------- ---------- --------------------------------------- --------------------------------------- ---------------------------------------
 1                    0001       张三         5.00                                    NULL                                    NULL
 2                    0001       张三         NULL                                    1.00                                    NULL
 3                    0001       张三         NULL                                    1.00                                    3.00
 1                    0002       李四         10.00                                   NULL                                    NULL
 2                    0002       李四         10.00                                   NULL                                    NULL
 3                    0002       李四         10.00                                   NULL                                    30.00
 1                    0003       王五         20.00                                   NULL                                    NULL
 2                    0003       王五         NULL                                    10.00                                   NULL
 3                    0003       王五         NULL                                    10.00                                   0.00
 
 (9 行受影响)
 
 
 */

------解决方案--------------------
SQL code

declare @T table 
(kh varchar(4),xm varchar(4),cr numeric(4,2),xf varchar(5),ye numeric(4,2))
insert into @T
select '0001','张三',5.00,null,3.00 union all
select '0001','张三',null,'1.O0',3.00 union all
select '0001','张三',null,'1.00',3.00 union all
select '0002','李四',10.00,null,30.00 union all
select '0002','李四',10.00,null,30.00 union all
select '0002','李四',10.00,null,30.00 union all
select '0003','王五',20.00,null,0.00 union all
select '0003','王五',null,'10.00',0.00 union all
select '0003','王五',null,'10.00',0.00

;with maco as 
(
select 
    row_number() over (partiti