相同列合并问题

[xingn]

如：

kh xm cr xf ye
0001 张三 5.00 NULL 3.00
0001 张三 NULL 1.O0 3.00
0001 张三 NULL 1.00 3.00
0002 李四 10.00 NULL 30.00
0002 李四 10.00 NULL 30.00
0002 李四 10.00 NULL 30.00
0003 王五 20.00 NULL 0.00
0003 王五 NULL 10.00 0.00
0003 王五 NULL 10.00 0.00

要变成


kh xm cr xf ye
0001 张三 5.00 NULL NULL
0001 张三 NULL 1.O0 NULL
0001 张三 NULL 1.00 3.00
0002 李四 10.00 NULL NULL
0002 李四 10.00 NULL NULL
0002 李四 10.00 NULL 30.00
0003 王五 20.00 NULL NULL
0003 王五 NULL 10.00 NULL
0003 王五 NULL 10.00 0.00


请问要怎么写。谢谢指教

[發糞塗牆]

结果是弄出来了，但是我不知道你合并的依据是什么，比如为什么是每组的最后一条才保留？

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 行受影响)
 
 
 */

[叶子]

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 (partition by xm,ye order by getdate()) as id,
	*,
	(select count(*) from @T where xm=t.xm and ye=t.ye) as cnt
from @T t
)

select kh,cr,xf,case when id=cnt then ye else null end as ye from maco
/*
kh   cr       xf    ye
---- -------- ----- ---------
0002 10.00    NULL  NULL
0002 10.00    NULL  NULL
0002 10.00    NULL  30.00
0003 20.00    NULL  NULL
0003 NULL     10.00 NULL
0003 NULL     10.00 0.00
0001 5.00     NULL  NULL
0001 NULL     1.O0  NULL
0001 NULL     1.00  3.00
*/

[叶子]

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 union all
select '0003','王五',null,'10.00',5.00 union all --新添加2条数据
select '0003','王五',null,'10.00',5.00 

;with maco as 
(
select 
	row_number() over (partition by xm,ye order by getdate()) as id,
	*,
	(select count(*) from @T where xm=t.xm and ye=t.ye) as cnt
from @T t
)

select kh,cr,xf,case when id=cnt then ye else null end as ye 
from maco order by 1
--添加2条数据后的结果
/*
kh   cr      xf    ye
---- ------- ----- --------
0001 5.00    NULL  NULL
0001 NULL    1.O0  NULL
0001 NULL    1.00  3.00
0002 10.00   NULL  NULL
0002 10.00   NULL  NULL
0002 10.00   NULL  30.00
0003 20.00   NULL  NULL
0003 NULL    10.00 NULL
0003 NULL    10.00 0.00
0003 NULL    10.00 NULL
0003 NULL    10.00 5.00
*/