相同列合并问题
如：
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
请问要怎么写。谢谢指教
[解决办法]
结果是弄出来了，但是我不知道你合并的依据是什么，比如为什么是每组的最后一条才保留？
- SQL code
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 @Tselect '0001','张三',5.00,null,3.00 union allselect '0001','张三',null,'1.O0',3.00 union allselect '0001','张三',null,'1.00',3.00 union allselect '0002','李四',10.00,null,30.00 union allselect '0002','李四',10.00,null,30.00 union allselect '0002','李四',10.00,null,30.00 union allselect '0003','王五',20.00,null,0.00 union allselect '0003','王五',null,'10.00',0.00 union allselect '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 cntfrom @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 NULL0002 10.00 NULL NULL0002 10.00 NULL 30.000003 20.00 NULL NULL0003 NULL 10.00 NULL0003 NULL 10.00 0.000001 5.00 NULL NULL0001 NULL 1.O0 NULL0001 NULL 1.00 3.00*/
[解决办法]
- SQL code
declare @T table (kh varchar(4),xm varchar(4),cr numeric(4,2),xf varchar(5),ye numeric(4,2))insert into @Tselect '0001','张三',5.00,null,3.00 union allselect '0001','张三',null,'1.O0',3.00 union allselect '0001','张三',null,'1.00',3.00 union allselect '0002','李四',10.00,null,30.00 union allselect '0002','李四',10.00,null,30.00 union allselect '0002','李四',10.00,null,30.00 union allselect '0003','王五',20.00,null,0.00 union allselect '0003','王五',null,'10.00',0.00 union allselect '0003','王五',null,'10.00',0.00 union allselect '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 cntfrom @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 NULL0001 NULL 1.O0 NULL0001 NULL 1.00 3.000002 10.00 NULL NULL0002 10.00 NULL NULL0002 10.00 NULL 30.000003 20.00 NULL NULL0003 NULL 10.00 NULL0003 NULL 10.00 0.000003 NULL 10.00 NULL0003 NULL 10.00 5.00*/