php - 如何避免对mysql查询进行子查询

Question

有4张 table

team
---------------
id | teamname |
1  | lal      |
2  | sobuj    |

member
-----------------------
id | membername| team |
1  | sagor     | 1    |
2  | sumon     | 1    |

dps
---------------------------------------------------
id | member| team | deposit | refund | ddate      |
1  | 1     | 1    | 100     | 12     | 2016-09-01 |
2  | 2     | 1    | 120     | 34     | 2016-09-01 |
3  | 1     | 1    | 130     | 22     | 2016-09-01 |
4  | 3     | 1    | 120     | 0      | 2016-09-01 |

mvs
----------------------------------------------------
id | member| team | mvs_total| refund | ddate      |
1  | 1     | 1    | 100      | 12     | 2016-09-01 |
2  | 2     | 1    | 120      | 34     | 2016-09-01 |
3  | 1     | 1    | 130      | 22     | 2016-09-01 |
4  | 3     | 1    | 120      | 0      | 2016-09-01 |

这是我的查询:

$result=mysql_query(select * from member where team='1');
$row=mysql_fetch_array($result);
do{
    $aresult=mysql_query(select sum(deposit)-sum(refund)as balance from dps where member='$row[id]');
    $arow=mysql_fetch_array($aresult);
    echo $arow[balance];

    $bresult=mysql_query(select sum(mvs_total)as mvs_balance from mvs where member='$row[id]' and date1<='2016-09-01');
    $brow=mysql_fetch_array($bresult);
    echo $brow[mvs_total];

} while($row=mysql_fetch_array($result));

它花费了太多时间如何避免子查询或我如何节省时间建议请提前致谢。

Answer 1

我在您的表结构中注意到的第一件事是列的重复。
例如。 team列在dps和mcs表中不需要，因为从member列[connect to member table ]你可以很容易的找到dps或者mvs在哪个队下。

对于您当前的问题，您可以使用以下查询。

    SELECT member.id, 
       member.membername, 
       Sum(dps.deposit) - Sum(dps.refund)       AS balance, 
       Sum(mvs.mvs_total)                       AS mvs_balance, 
       Sum(loan.deposit) - Sum(loan.withdraw)   AS balance, 
       Sum(loan.deposit2) - Sum(loan.withdraw2) AS balance2, 
       Sum(loan.total) - Sum(loan.lpay)         AS outsstanding 
FROM   member AS member 
       LEFT JOIN dps AS dps 
              ON dps.member = member.id 
       LEFT JOIN mvs AS mvs 
              ON mvs.member = member.id 
       LEFT JOIN loan AS loan 
              ON loan.member = member.id 
       WHERE  member.team = 1 
       AND mvs.ddate <= '2016-09-01' 
       -- AND loan.team = '10'  
       -- AND loan.cs = 'Core' 
GROUP  BY member.id