使用Union或Join在where语句中使用多个查询结果 - Use Union or Join to use multiple query results in a where statement

Use Union or Join to use multiple query results in a where statement

我需要组合3求和查询的结果，并在循环中回显结果。我不知道该怎么做才对。我想使用一个while循环来为每个客户端回显一行，显示按客户端名称分组的3求和查询的结果:

客户名称|应收账款总额|现金销售应收账款总额|赊销销售应收账款总额

result显示所有由于客户端。非现金结果显示所有由于客户的信用销售。cashresult显示客户现金销售的所有款项。

$result = mysql_query("SELECT `client_name` As `Client`, SUM(`due_to_client`) As `Total Due` 
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' 
GROUP BY `client_name`"); 
$noncashresult = mysql_query("SELECT `client_name` As `Client`, SUM(`due_to_client`) As `Credit Total Due`
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' AND `paymenttype` IN ('Credit') 
GROUP BY `client_name`");
$cashresult = mysql_query("SELECT `client_name` As `Client`, SUM(`due_to_client`) As `Cash Total Due`
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' AND `paymenttype` IN ('Cash') 
GROUP BY `client_name`");
while($row = mysql_fetch_array($result))
                    {
                echo $row['Client'];
                echo $row['Total Due'];
                echo $row['Credit Total Due'];
                echo $row['Cash Total Due'];
                    }

你可以这样写:

SELECT `client_name` As `Client`,
SUM(`due_to_client`) As `Total Due`,
SUM(CASE WHEN `paymenttype`='Credit' THEN `due_to_client` ELSE 0 END) As `Credit Total Due`,
SUM(CASE WHEN `paymenttype`='Cash' THEN `due_to_client` ELSE 0 END) As `Cash Total Due`
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' 
GROUP BY `client_name`

这应该产生结果:

Client | Total Due | Credit Total Due | Cash Total Due

当然，尽量避免使用mysql_*扩展名，而这在mysql 5.5.x中已被弃用。使用mysqli或pdo扩展名