求助关于mySql查询的问题 高分

Question

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

Answer 1

你能把SQL的逻辑需求描述得清楚一些嘛？

Answer 2

SELECT 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;

Answer 3

mysql>    SELECT 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)

Answer 4

有下面这个SQL 在mySQL中怎么实现？
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

Answer 5

如果是MySQL5.0以下就不支持这种方法！