select invoice.InvoiceCode,invoice.PartnerCode,invoice.InvoiceDate,invoice.InvoicePaid,sum(ifnull(invoicelist.Amount,0)) + sum(ifnull(invoiceuser.Amount,0)) as Total_Amount from invoice left join invoicelist on (invoice.InvoiceCode=invoicelist.InvoiceCode) left join invoiceuser on (invoice.InvoiceCode=invoiceuser.InvoiceCode) where 1=1 group by invoice.InvoiceCode,invoice.PartnerCode,invoice.InvoiceDate,invoice.InvoicePaid order by invoice.InvoiceCode Desc
解决方案 »
- thinkphp中的fck编辑器的问题
- xajax新手求救xajaxproject.org的官方 例子 clickme没反应
- 从LDAP获取邮箱列表
- 十万火急!求大神们教我如何在本机上调试该PHP网站!在线等。。。
- 表的处理
- 可恶的单线程的php socket!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- html页面foreach循环如何分段?
- Parse error: syntax error, unexpected '0' (T_LNUMBER) in F:\wamp\www\book_club\v
- 怎样才能根据用户的权限load出相应的菜单?
- 在php中,如何让客户端能够提取到最新的数据,而不是调用浏览器缓存中的数据?(菜鸟在csdn的"处女提问",请大家支持一下)
- 请高手帮忙看看这条 SQL 语句!!谢谢
- 求一个正则表达式
IF((sum(l.Amount) IS NULL),0,sum(l.Amount)) + IF((sum(u.Amount) IS NULL),0,sum(u.Amount))
FROM invoice as i
LEFT JOIN invoicelist as l
ON i.InvoiceCode = l.InvoiceCode
LEFT JOIN invoiceuser as u
ON i.InvoiceCode = u.InvoiceCode
GROUP BY i.InvoiceCode;
-> IF((sum(l.Amount) IS NULL),0,sum(l.Amount)) + IF((sum(u.Amount)
IS NULL),0,sum(u.Amount))
-> FROM invoice as i
-> LEFT JOIN invoicelist as l
-> ON i.InvoiceCode = l.InvoiceCode
-> LEFT JOIN invoiceuser as u
-> ON i.InvoiceCode = u.InvoiceCode
-> GROUP BY i.InvoiceCode;
+--------------+----------------------------------------------------------------
---------------------------+
| InvoiceCode | IF((sum(l.Amount) IS NULL),0,sum(l.Amount)) + IF((sum(u.Amount)
IS NULL),0,sum(u.Amount)) |
+--------------+----------------------------------------------------------------
---------------------------+
| IN2007031001 |
10410 |
| IN2007031002 |
222 |
| IN2007031003 |
1260 |
+--------------+----------------------------------------------------------------
---------------------------+
3 rows in set (0.02 sec)
SELECT Invoice.InvoiceCode, sum( tba.aa ) AS taa
FROM Invoice
LEFT JOIN ( ( SELECT InvoiceCode, sum( ifnull( Amount, 0 ) ) AS aa
FROM invoicelist
GROUP BY InvoiceCode )UNION ( SELECT InvoiceCode, sum( ifnull( Amount, 0 ) ) AS aa
FROM invoiceuser
GROUP BY InvoiceCode )
) AS tba ON ( Invoice.InvoiceCode = tba.InvoiceCode )
GROUP BY Invoice.InvoiceCode