如果按简单的条件判断可现实现,但效率低望高手帮帮忙!表A结构如下F1 F2 F3 F4 F5 F6 F7
1 NULL 3 4 5 5 7
2 4 NULL NULL NULL 6 9
1 2 NULL NULL NULL NULL NULL
1 2 NULL NULL NULL NULL NULL
...........表B结构类似
Code TheDate F1 F2 F3 F4 F5 F6 F7
001 2008-1-1 1 NULL 3 4 5 6 7
....
009 2008-1-1 2 4 NULL NULL NULL NULL NULL(这条等于表A的第3和第4的汇总)即表B是汇总表A中完全相同的F1-F7字段,也就是说,在逐行扫描表A是,如果发现表B中的F1-F7字段值完全相同(包括空值),则汇总,如果不相同则插入.
1 NULL 3 4 5 5 7
2 4 NULL NULL NULL 6 9
1 2 NULL NULL NULL NULL NULL
1 2 NULL NULL NULL NULL NULL
...........表B结构类似
Code TheDate F1 F2 F3 F4 F5 F6 F7
001 2008-1-1 1 NULL 3 4 5 6 7
....
009 2008-1-1 2 4 NULL NULL NULL NULL NULL(这条等于表A的第3和第4的汇总)即表B是汇总表A中完全相同的F1-F7字段,也就是说,在逐行扫描表A是,如果发现表B中的F1-F7字段值完全相同(包括空值),则汇总,如果不相同则插入.
select distinct * from ta
insert @ta select
1,NULL,3,4,5, 5,7 union all select
2,4,NULL,NULL, NULL,6,9 union all select
1,2,NULL,NULL, NULL, NULL, NULL union all select
1,2,NULL,NULL, NULL, NULL, NULL
declare @tb table(code varchar(3),thedate varchar(10),F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int)
insert @tbselect right('000'+ltrim(px),3),convert(char(10),getdate(),120),F1 ,F2 ,F3 ,F4 ,F5 ,F6 ,F7
from
(
select a.F1 ,a.F2 ,a.F3 ,a.F4 ,a.F5 ,a.F6 ,a.F7,
px = (select count(1) + 1
from (select distinct *
from @ta) b
where checksum(F1 ,F2 ,F3 ,F4 ,F5 ,F6 ,F7 )<
checksum(a.F1 ,a.F2 ,a.F3 ,a.F4 ,a.F5 ,a.F6 ,a.F7 ))
from (select distinct *
from @ta) a) cselect * from @tb
/*
code thedate F1 F2 F3 F4 F5 F6 F7
---- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
002 2008-03-01 1 NULL 3 4 5 5 7
003 2008-03-01 1 2 NULL NULL NULL NULL NULL
001 2008-03-01 2 4 NULL NULL NULL 6 9(所影响的行数为 3 行)
*/
insert @ta select
1,NULL,3,4,5, 5,7 union all select
2,4,NULL,NULL, NULL,6,9 union all select
1,2,NULL,NULL, NULL, NULL, NULL union all select
1,2,NULL,NULL, NULL, NULL, NULL
declare @tb table(code varchar(3),thedate varchar(10),F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int)
insert @tb
select
px = right('000'+ltrim((select count(1) + 1
from (select distinct *
from @ta) b
where checksum(F1 ,F2 ,F3 ,F4 ,F5 ,F6 ,F7 )<
checksum(a.F1 ,a.F2 ,a.F3 ,a.F4 ,a.F5 ,a.F6 ,a.F7 ))),3),
convert(char(10),getdate(),120),
F1 ,F2 ,F3 ,F4 ,F5 ,F6 ,F7
from (select distinct *
from @ta) a
select * from @tb
/*
code thedate F1 F2 F3 F4 F5 F6 F7
---- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
002 2008-03-01 1 NULL 3 4 5 5 7
003 2008-03-01 1 2 NULL NULL NULL NULL NULL
001 2008-03-01 2 4 NULL NULL NULL 6 9(所影响的行数为 3 行)
*/
INSERT @t SELECT 1,NULL,3,4,5, 5,7
UNION ALL SELECT 2,4,NULL,NULL, NULL,6,9
UNION ALL SELECT 1,2,NULL,NULL, NULL, NULL, NULL
UNION ALL SELECT 1,2,NULL,NULL, NULL, NULL, NULL SELECT IDENTITY(INT) ID,* INTO # FROM (SELECT DISTINCT F1,F2,F3,F4,F5,F6,F7 FROM @t) XSELECT RIGHT('000' + RTRIM(ID),3) code,CONVERT(VARCHAR(10),GETDATE(),120) date,F1,F2,F3,F4,F5,F6,F7 FROM #DROP TABLE #
insert @ta select
1,NULL,3,4,5, 5,7 union all select
2,4,NULL,NULL, NULL,6,9 union all select
1,2,NULL,NULL, NULL, NULL, NULL union all select
1,2,NULL,NULL, NULL, NULL, NULL --为了不改变表顺序用binary_checksum作为组:select code=cast('' as char(3)), thedate=getdate(),
sum(F1)F1,sum(F2)F2,sum(F3)F3,sum(F4)F4,sum(F5)F5,sum(F6)F6,sum(F7)F7
into #T1 from @Ta
group by binary_checksum(F1,F2,F3,F4,F5,F6,F7)--生成临时表#T1--更新Code
declare @i int
set @i=0
update #t1
set Code=right(1000+@i,3),@i=@i+1--查询select * from #T1--把临时表导入B表--drop table #T1
/*
code thedate F1 F2 F3 F4 F5 F6 F7
---- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- -----------
001 2008-03-01 22:42:38.837 2 4 NULL NULL NULL 6 9
002 2008-03-01 22:42:38.837 1 NULL 3 4 5 5 7
003 2008-03-01 22:42:38.837 2 4 NULL NULL NULL NULL NULL(所影响的行数为 3 行)
*/
insert @ta select 1,NULL,3,4,5, 5,7
union all select 2,4,NULL,NULL, NULL,6,9
union all select 1,2,NULL,NULL, NULL, NULL, NULL
union all select 1,2,NULL,NULL, NULL, NULL, NULL
declare @tb table(code varchar(3),thedate varchar(10),F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int)insert @TB SELECT '001','2008-01-01',
count(*)*F1,count(*)*F2,count(*)*F3,count(*)*F4,count(*)*F5,count(*)*F6,count(*)*F7
FROM @TA GROUP BY F1,F2,F3,F4,F5,F6,F7SELECT * FROM @TB
/*
code thedate F1 F2 F3 F4 F5 F6 F7
---- ---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
001 2008-01-01 1 NULL 3 4 5 5 7
001 2008-01-01 2 4 NULL NULL NULL NULL NULL
001 2008-01-01 2 4 NULL NULL NULL 6 9
*/