有三个数据表，分别为 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 ，谢谢大家!


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

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

引用:

以下是引用 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

不对啊~

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

谢谢!

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



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

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