请教高手关于两表复杂查询统计更新字段问题
有2个表，分别是：table1和table2
table1里面有字段ID、AA、BB、CC、DD、EE、FF、GG、SUM、LSNUM
table2里面有字段ID、AA、BB、CC、DD、EE、FF、GG
ID字段是自增加1，其他字段全部是数字型。
现在要做的就是将table2里面的AA、BB、CC、DD、EE、FF、GG和table1里面的AA、BB、CC、DD、EE、FF、GG相比，如果两个表的AA、BB、CC、DD、EE、FF、GG这几个字段的值完成相同的话，则将table1里面的SUM进行+1；
如果不同的话，则再进行判断，table2里面的AA、BB、CC、DD、EE、FF、GG字段里面的数字在table1里面AA、BB、CC、DD、EE、FF、GG里面出现的次数，并将出现的次数统计到LSNUM里面。如下：
table1表：
ID AA BB CC DD EE FF GG SUM LSNUM
1 100 600 1000 2000 3000 4000 8000 0 0
2 300 500 600 1000 4000 6000 8000 0 0
table2
ID AA BB CC DD EE FF GG
1 100 600 1000 2000 3000 4000 8000
希望通过SQL查询更新后，table1表中的SUM、LSNUM值将变化为以下：
table1表：
ID AA BB CC DD EE FF GG SUM LSNUM
1 100 600 1000 2000 3000 4000 8000 1 0
2 300 500 600 1000 4000 6000 8000 0 4
第一条记录完全相同，所以SUM的值+1
第二条记录有4个数字相同，则LSNUM=4
[解决办法]
select ', '+cast(aa as varchar)+ ', '+cast(bb as varchar)+ ', '+cast(cc as varchar)+ ', '+cast(dd as varchar)+ ', '+cast(ee as varchar)+ ', '+cast(ff as varchar)+ ', '+cast(gg as varchar)+ ', ' as indexchar
into #tmp from table1
update t1 set [sum]=t2.total
from table1 t1 inner join
(select aa,bb,cc,dd,ee,ff,gg,count(1) total from table2 group by aa,bb,cc,dd,ee,ff,gg) t2
on t1.aa=t2.aa and t1.bb=t2.bb and t1.cc=t2.cc and t1.dd=t2.dd and t1.ee=t2.ee and t1.ff=t2.ff and t1.gg=t2.gg
update t2 set lsum=a+b+c+e+f+g
from t2 inner join
(
select t2.aa,t2.bb,t2.cc,t2.dd,t2.ee,t2.ff,t2.gg case when charindex( ', '+cast(aa as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end a,
case when charindex( ', '+cast(bb as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end b,
case when charindex( ', '+cast(cc as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end c,
case when charindex( ', '+cast(dd as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end d,
case when charindex( ', '+cast(ee as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end e,
case when charindex( ', '+cast(ff as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end f,
case when charindex( ', '+cast(gg as varchar)+ ', ',tmp.indexchar))> 0 then 1 else 0 end g,
from table2 t2, #tmp tmp
)tt on tt.aa=t2.aa and tt.bb=t2,bb and tt.cc=t2.cc and tt.dd=t2.dd and tt.ee=t2.ee and tt.ff=t2.ff
没有测试,你自已试下
[解决办法]
/*
1、第一条记录完全相同，所以SUM的值+1
2、第二条记录有4个数字相同，则LSNUM=4
*/
----1、
Update A
Set [Sum]=B.[Counts]
From
Table1 As A,(Select AA,BB,CC,DD,EE,FF,GG,Count(1) As [Counts]
From Table2 Group By AA,BB,CC,DD,EE,FF,GG) As B
Where A.AA=B.AA And A.BB=B.BB And A.CC=B.CC And
A.DD=B.DD And A.EE=B.EE And A.FF=B.FF And A.GG=B.GG
----2、
Update A
Set LSNUM=B.A+B.B+B.C+B.D+B.E+B.F+B.G
From Table1 As A,(
Select A.AA,A.BB,A.CC,A.DD,A.EE,A.FF,A.GG,
Case When CharIndex( ', '+rtrim(B.AA)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As A,
Case When CharIndex( ', '+rtrim(B.BB)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As B,
Case When CharIndex( ', '+rtrim(B.CC)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As C,
Case When CharIndex( ', '+rtrim(B.DD)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As D,
Case When CharIndex( ', '+rtrim(B.EE)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As E,
Case When CharIndex( ', '+rtrim(B.FF)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As F,
Case When CharIndex( ', '+rtrim(B.GG)+ ', ', ', '+rtrim(A.AA)+ ', '+rtrim(A.BB)+ ', '+rtrim(A.CC)
+ ', '+rtrim(A.DD)+ ', '+rtrim(A.EE)+ ', '+rtrim(A.FF)+ ', '+rtrim(A.GG)+ ', ')> 0 Then 1 Else 0 End As G
From Table1 As A,Table2 As B Where Not Exists
(Select 1 From Table2 Where AA=A.AA And BB=A.BB And CC=A.CC And
DD=A.DD And EE=A.EE And FF=A.FF And GG=A.GG)
) As B
Where A.AA=B.AA And A.BB=B.BB And A.CC=B.CC And A.DD=B.DD And A.EE=B.EE And A.FF=B.FF And A.GG=B.GG
