我有一个类似的sql查询。。。
SELECT c.clientid, c.clientname, c.billingdate,
(SELECT ifnull(sum(total), 0)
FROM invoice i
WHERE i.client = c.clientid AND i.isdeleted = 0) -
(SELECT ifnull(sum(p.amount), 0)
FROM payment p
INNER JOIN invoice i ON p.invoice = i.invoiceid
WHERE i.client = c.clientid and i.isdeleted = 0) as balance,
CASE c.isactive+0 WHEN '1' THEN 'Stop'
ELSE 'Start' END as Active
FROM client c
ORDER BY clientname
这很好，没有错误，但请注意这一部分。。。。
(SELECT ifnull(sum(total), 0)
FROM invoice i
WHERE i.client = c.clientid AND i.isdeleted = 0) -(SELECT ifnull(sum(p.amount), 0)
FROM payment p
INNER JOIN invoice i ON p.invoice = i.invoiceid
WHERE i.client = c.clientid AND i.isdeleted = 0) as balance
我写了一个PHP脚本。。。
if($remaining < 0){
$remaining = $row['total'];
}else{
$remaining = $remaining + $row['total'];
}
我想做的是将我在PHP中编写的内容合并到SQL查询中，但我以前从未使用if语句编写过SQL查询（如果允许的话）。如何将我的PHP脚本合并到SQL查询中？有什么建议吗？
没有if，但有case，它允许您做几乎相同的事情。您已经在查询中使用了case，所以我想您知道它是如何工作的。：）
您可以包装结果并使用它。确保合计和剩余是结果的一部分。
SELECT tt.clientid, tt.clientname, tt.billingdate, tt.total, tt.remaining, tt.active
FROM (
... here goes all of your select but the the order by
) tt
ORDER BY tt.clientid
有了这个结构，你可以做你在PHP 中所做的事情
SELECT tt.clientid, tt.clientname, tt.billingdate, tt.total, tt.active,
CASE WHEN tt.remaining < 0 then tt.total
else tt.remaining - tt.total
END as remaining
FROM (
... here goes all of your select make sure you select a total and a remaining
) tt
ORDER BY tt.clientid
所以实际上，您所做的是创建一个临时视图。不确定你的数据模型，但我认为它看起来像这个
SELECT tt.clientid, tt.clientname, tt.billingdate, tt.total, tt.active,
CASE WHEN tt.remaining < 0 then tt.total
else tt.remaining - tt.total
END as remaining
FROM (
SELECT c.clientid, c.clientname, c.billingdate,
(SELECT ifnull(sum(total), 0)
FROM invoice i
WHERE i.client = c.clientid AND i.isdeleted = 0) as total,
(SELECT ifnull(sum(total), 0)
FROM invoice i
WHERE i.client = c.clientid AND i.isdeleted = 0) -
(SELECT ifnull(sum(p.amount), 0)
FROM payment p
INNER JOIN invoice i ON p.invoice = i.invoiceid
WHERE i.client = c.clientid and i.isdeleted = 0) as remaining,
CASE c.isactive+0 WHEN '1' THEN 'Stop'
ELSE 'Start' END as Active
FROM client c
) TT
ORDER BY clientname