Sql数据合并问题(急啊)
有两行数据:
fid hid sid td1 td2 td3 td4
1 2 4 11 22 null null
1 2 5 null null 33 44
想要得到的结果:
fid hid td1 td2 td3 td4
1 2 11 22 33 44
[解决办法]
declare @tb table(fid int,hid int,sid int,td1 int,td2 int,td3 int,td4 int)insert into @tb select 1,2,4,11,22,null,nullinsert into @tb select 1,2,5,null,null,33,44select fid,hid,isnull(max(td1),min(td1)) as td1,isnull(max(td2),min(td2)) as td2,isnull(max(td3),min(td3)) as td3,isnull(max(td4),min(td4)) as td4from @tb group by fid,hid
[解决办法]
DECLARE @t TABLE(fid int,hid int,sid int,td1 int,td2 int,td3 int,td4 int)INSERT @t SELECT 1, 2 , 4, 11 , 22 , null, null UNION ALL SELECT 1 , 2, 5 , null , null, 33 , 44 UNION ALL SELECT 1, 2, 1, 24,null,null,nullUNION ALL SELECT 2, 1, null,2,1,1,1SELECT fid,hid,(SELECT TOP 1 td1 FROM @t WHERE fid=a.fid AND hid=a.hid AND td1 IS NOT NULL) td1 /*td2,td3,td4类同*/ FROM @t aGROUP BY fid,hid