嵌套查询语法改进

Question

使用1个查询而不是2个查询的正确语法是什么？

$result = mysql_query(SELECT * FROM users);

while($row = mysql_fetch_array($result)){
    $result = mysql_query(SELECT SUM(balance) FROM users_account WHERE uid=$row[id]);
}

Answer 1

这将返回user_account表中包含用户信息的所有记录:-

    SELECT SUM(ua.balance),u.*,ua.* FROM users_account ua
    left join users u on ua.users_account=u.id
    GROUP BY u.id

Answer 2

您应该对表和group by user表进行内部联接。您可以在SELECT子句中添加更多列，请记住将相同的列也添加到GROUP BY子句以获得标准的SQL语句。

$query = " SELECT u.uid, SUM(a.balance) 
           FROM users_account a
           INNER JOIN users  u
           ON u.uid = a.uid
           GROUP BY u.uid";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
     ...

如果需要，排列$query连接字符串。

Answer 3

$result = mysql_query(SELECT a.*, b.SUM(balance) AS user_balance FROM users a, users_account b WHERE uid=$row[id]);