取最全项的有关问题

取最全项的问题
之前一个问题没有大神回答，我自己想出的办法现在有个新问题。


有一表
ID name var1var2 var3
1 A null A2 A3
2 B B2 null
3 B B1 B2 null
4 C null C2 C3
5 C C1 null
6 C C1 C2 C3

我需要选择出如下项，简单的说就是补全信息删除重复的项

ID name var1var2 var3
1 A null A2 A3
3 B B1 B2 null
6 C C1 C2 C3

如何用SQL实现？

[解决办法]

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[name] varchar(1),[var1] varchar(2),[var2] varchar(2),[var3] varchar(2))insert [test]select 1,'A',null,'A2','A3' union allselect 2,'B','B2',null,null union allselect 3,'B','B1','B2',null union allselect 4,'C',null,'C2','C3' union allselect 5,'C','C1',null,null union allselect 6,'C','C1','C2','C3'with tas(select *,   case when [var1] is null then 1 else 0 end+   case when [var2] is null then 1 else 0 end+   case when [var3] is null then 1 else 0 end as totalsfrom test)select [ID],[name],[var1],[var2],[var3] from t awhere totals=(select MIN(totals) from t b where a.name=b.name)/*ID    name    var1    var2    var31    A    NULL    A2    A33    B    B1    B2    NULL6    C    C1    C2    C3*/