多关系表求和sum的问题(页 1) - SQL Server论坛

foshan 发表于 2008-3-5 17:48

多关系表求和sum的问题

有三个数据表，分别为 A  、AA  、AAA ,表AA、表AAA的外键与表 A 的主键ID 关联
表A
      ID       name
      1       me

表AA
      ID       A_ID    L1
      1          1       10
      2          1       10
      3          1       10

表AAA
      ID       AA_ID    L2
      1          1       15

现在要求字段L1、字段L2 之和， SQL语句如下：
select sum(L1)as s,sum(L2) as ss from A
left join AAA on A.ID=AA_ID
left join AA on A.ID=A_ID
where A.ID=1
目的是想实现求和后 sum(L1) =30 、 sum(L2) =15，但执行查询后得出的结果是sum(L1) =30 、 sum(L2) =45 。sum(L1)求和就正确，但sum(L2)求和就比要的数大了3倍。而且这个增加的倍数是随着表AA中相同的A_ID的数量成正比的。
请问，如何写出正确的SQL语句才能求出 sum(L1) =30 、 sum(L2) =15 ，谢谢大家!
[attach]32762[/attach]

[[it] 本帖最后由 foshan 于 2008-3-6 08:16 编辑 [/it]]

provoke 发表于 2008-3-6 13:35

select sum(L1)as s,sum(L2) as ss from A,AA,AAA
where AA_ID=A.ID and A_id=A.id and A.id=1

foshan 发表于 2008-3-6 17:50

[quote][bo]以下是引用 [un]provoke[/un] 在 2008-3-6 13:35 的发言：[/bo]

select sum(L1)as s,sum(L2) as ss from A,AA,AAA
where AA_ID=A.ID and A_id=A.id and A.id=1 [/quote]

不对啊~
[attach]32791[/attach]

zlhgo 发表于 2008-3-7 19:16

Select B.S,C.SS From A

LEFT JOIN (Select AA_ID,SUM(L1) AS S From AA Group By AA_ID) AS B ON A.ID=B.AA_ID

LEFT JOIN (Select AAA_ID,SUM(L2) AS SS From AAA Group By AAA_ID) AS C ON A.ID=C.AAA_ID

Where A.ID=1

foshan 发表于 2008-3-10 14:11

谢谢!

Select B.S,C.SS From A

LEFT JOIN (Select A_ID,SUM(L1) AS S From AA Group By A_ID) AS B ON A.ID=B.A_ID

LEFT JOIN (Select AA_ID,SUM(L2) AS SS From AAA Group By AA_ID) AS C ON A.ID=C.AA_ID

Where A.ID=1

[attach]32873[/attach]

[[it] 本帖最后由 foshan 于 2008-3-10 14:12 编辑 [/it]]

yuwenxiang 发表于 2008-3-27 20:48

高深的东西
3表连接，高手能讲解是怎么回事吗？

页: [1]