查询语句代码1：

    select
    TransportPlans.PlanID,
    (select sum(Orders.PSettleAmount)as WenQian from Orders,TransportPlans where (Orders.SettleType = 0 or Orders.SettleType = 3) and Orders.PlanID = TransportPlans.PlanId) as WenQian ,
    (select sum(Orders.PSettleAmount)as WenShou from Orders,TransportPlans where Orders.SettleType = 1 and Orders.PlanID = TransportPlans.PlanId ) as WenShou,
    (select sum(Orders.PSettleAmount)as WaiFu from Orders,TransportPlans where Orders.SettleType = 2 and Orders.PlanID = TransportPlans.PlanId ) as WaiFu

    from Orders,TransportPlans

    where Orders.PlanID = TransportPlans.PlanId  and TransportPlans.PlanID = '20101109001'

结果1：

问题：出现了两条一样的语句。。而且查询出来的结果是错误的，结果应该是：
查询语句代码2：

select Orders.SettleType,Orders.PSettleAmount from Orders where PlanID = '20101109001'

结果2：


照理说查询一出来的结果应该是，并且只有一条记录
PlanId         WenQian WenShou WaiFu
20101109001     1000    7178    Null

希望高手帮忙下！


[ 本帖最后由 等待冰柠檬 于 2010-12-8 16:43 编辑 ]

试试这个 -

    select
    TransportPlans.PlanID,

    (select sum(Orders.PSettleAmount) as WenQian
    from Orders where (Orders.SettleType = 0 or Orders.SettleType = 3) and
    Orders.PlanID = TransportPlans.PlanId) as WenQian ,

    (select sum(Orders.PSettleAmount) as WenShou
    from Orders where Orders.SettleType = 1 and Orders.PlanID = TransportPlans.PlanId ) as WenShou,

    (select sum(Orders.PSettleAmount)as WaiFu
    from Orders   where Orders.SettleType = 2 and Orders.PlanID = TransportPlans.PlanId ) as WaiFu
 
   from TransportPlans
    where  TransportPlans.PlanID = '20101109001'

[ 本帖最后由 png 于 2010-12-9 05:55 编辑 ]