求教一个冗余表的查询问题（应该是符合2NF了吧，呵呵）

create table A(outbhid varchar(10),xmbhid varchar(10),[type] int)

insert into A select 'L000001','XM000001',0

union all select 'L000002','XM000002',0

union all select 'T000001','L000002',1



create table B(outbhid varchar(10),clbhid nvarchar(10),outnumber int)

insert into B select 'L000002','材料1',10

union all select 'L000002','材料2',18

union all select 'T000001','材料1',-5

go

select t1.outbhid,t2.clbhid,t2.outnumber+isnull(t4.outnumber,0) as outnumbers

from A t1 inner join B t2 on t1.outbhid=t2.outbhid

left join A t3 on t3.outbhid=t1.xmbhid

left join B t4 on t4.outbhid=t3.outbhid

where t1.xmbhid='XM000002' and t1.[type]=0



go

drop table A,B

/*

outbhid    clbhid     outnumbers

---------- ---------- -----------

L000002    材料1        10

L000002    材料2        18



(2 行受影响)



*/

SELECT B.clbhid,sum(b.outnumber)

FROM  B  INNER JOIN 

    (

    SELECT outbhid FROM A WHERE Type=0 AND xmbhid='XM000002'

            OR (

                Type=1 AND EXISTS (

                SELECT * FROM A as T

                WHERE T.xmbhid=A.outbhid

            )

    

    ) as Tmp

        ON B.outbhid =Tmp.outbhid

GROUP BY B.clbhid 

SELECT B.clbhid,sum(b.outnumber)

FROM  B  INNER JOIN 

	(

	SELECT outbhid FROM A WHERE Type=0 AND xmbhid='XM000002'

			OR (

				Type=1 AND EXISTS (

				SELECT * FROM A as T

				WHERE T.xmbhid=A.outbhid

			)

	

	) as Tmp

        ON B.outbhid =A.outbhid

GROUP BY B.clbhid 

		

SELECT B.clbhid,sum(b.outnumber)

FROM  B  INNER JOIN A

            ON B.outbhid =A.outbhid

WHERE  A.xmbhid='XM000002'

      OR

      EXISTS(

            SELECT * 

            FROM A  as T

            WHERE  A.outbhid=T.xmbhid

                   AND A.xmbhid='XM000002'

            )

                   

GROUP BY B.clbhid 

SELECT B.clbhid,sum(b.outnumber)

FROM   B  INNER JOIN A

            ON B.outbhid =A.outbhid

WHERE  A.xmbhid='XM000002'

GROUP BY B.clbhid