记录合并的复杂 查询，请高手来 分值可加！

[learnall]

Num name A B C D
11 张三 10 20
12 张三 15 30 12
14 李四 10 9
13 李四 12 8 10 17
15 张三 20 10
...

我需要的结果：
Num name A B C D
14 李四 12 8 9 17
15 张三 20 20 30 10
...
以Num最大数记录为准，name相同的记录合并数据，
num大的为空的字段 则取num最近的值填充 并且将结果插入到新的表中

[chinaandys]

select num=max(num),name,A=max(isnull(A,0)),B=max(isnull(B,0)),C=max(isnull(C,0)),D=max(isnull(C,0))
from 表 group by name

[sunshareforever]

一句话是写不出来的，

写过程吧

[lsxaa]

select num=max(num)
,name
,A=max(isnull(A,0))
,B=max(isnull(B,0))
,C=max(isnull(C,0))
,D=max(isnull(D,0))
from 表
group by name
order by num

[Andy__Huang]

select * into 新表
select * from tb a
inner join (select name,A=max(A),B=max(B),C=max(C),D=max(D) FROM tb group by name)b
on a.num=b.num and a.name=b.name

[chinaandys]

select a.num,B=isnull(a.B,b.B),C=isnull(a.C,b.C),D=isnull(a.D,b.D) from 表 a
left join (select name,A=max(A),B=max(B),C=max(C),D=max(D) FROM 表group by name)b
on a.name=b.name

[zjcxc]

--改一下要求,这样处理的效率高一些
--以Num最大数记录为准，name相同的记录合并数据
--num大的为空的字段 则取最大的值填充
select a1.Num,a1.Name
,A=case when isnull(a.A,'')='' then b.A else a.A end
,B=case when isnull(a.B,'')='' then b.B else a.B end
,C=case when isnull(a.C,'')='' then b.C else a.C end
,D=case when isnull(a.D,'')='' then b.D else a.D end
from(
select Num=max(Num),Name
from 表 group by Name
)a1,表 a,(
select Name,A=max(A),B=max(B),C=max(C),D=max(D)
from 表 group by Name
)b where a1.Num=a.Num and a1.Name=a.Name
and a1.Name=b.Name

[Andy__Huang]

改一點

select * into 新表
select * from tb a
inner join (select num=max(num),name,A=max(A),B=max(B),C=max(C),D=max(D) FROM tb group by name)b
on a.num=b.num and a.name=b.name

[lsxaa]

select aa.*
,A=isnull(bb.A,(select top 1 A from 表 where name=b.name and A<>'' and id<bb.id))
,B=isnull(bb.B,(select top 1 B from 表 where name=b.name and B<>'' and id<bb.id))
,C=isnull(bb.C,(select top 1 C from 表 where name=b.name and C<>'' and id<bb.id))
,D=isnull(bb.D,(select top 1 D from 表 where name=b.name and D<>'' and id<bb.id))
from (select select num=max(num),name
from 表
group by name ) aa
,表 bb
where aa.id=bb.id

[learnall]

没有理解对！ 不是所有字段取最大值 是如果字段为空，则取num最近的字段值，如果不为空，则保留原来的值

[lsxaa]

select aa.*
,A=isnull(bb.A,(select top 1 A from 表
where name=b.name and A<>'' and id<bb.id))
,B=isnull(bb.B,(select top 1 B from 表
where name=b.name and B<>'' and id<bb.id))
,C=isnull(bb.C,(select top 1 C from 表
where name=b.name and C<>'' and id<bb.id))
,D=isnull(bb.D,(select top 1 D from 表
where name=b.name and D<>'' and id<bb.id))
from ( select num=max(num),name
from 表
group by name ) aa
,表 bb
where aa.id=bb.id

[zjcxc]

select a1.Num,a1.Name
,A=case when isnull(a.A,'')='' then b.A
else (select top 1 A from 表 where name=a.name and isnull(A,'')<>'' order by abs(num-a.num)) end
,B=case when isnull(a.B,'')='' then b.B
else (select top 1 B from 表 where name=a.name and isnull(B,'')<>'' order by abs(num-a.num)) end
,C=case when isnull(a.C,'')='' then b.C
else (select top 1 C from 表 where name=a.name and isnull(C,'')<>'' order by abs(num-a.num)) end
,D=case when isnull(a.D,'')='' then b.D
else (select top 1 D from 表 where name=a.name and isnull(D,'')<>'' order by abs(num-a.num)) end
from(
select Num=max(Num),Name
from 表 group by Name
)a1,表 a
where a1.Num=a.Num and a1.Name=a.Name

[lsxaa]

楼主试试我最后写这个?

[lsxaa]

select aa.*
,A=isnull(bb.A,(select top 1 A from 表
where name=b.name and A<>'' and id<bb.id))
,B=isnull(bb.B,(select top 1 B from 表
where name=b.name and B<>'' and id<bb.id))
,C=isnull(bb.C,(select top 1 C from 表
where name=b.name and C<>'' and id<bb.id))
,D=isnull(bb.D,(select top 1 D from 表
where name=b.name and D<>'' and id<bb.id))
from ( select num=max(num),name
from 表
group by name ) aa
,表 bb
where aa.num=bb.num -是num 刚才写成id 了

[learnall]

对不起！ 大家有优化的么？我的数据有上百万条呀....

[zjcxc]

关于最大值,还是最近值,不是没有理解到

这两种情况我都写了处理语句,而写最大值的时候也说明了,是为了效率改了你的要求