我有兩個mysql表。
Invoice_details
invoice_no proje_ID invoice_Date invoice_amount1004 6
1005 4
1006 2
1007 2
1008 1
1009 7
received_amount_details[/span]。
invoice_no received_amount[/span
1004 205.00。
1004 40.00
1006 283.5
預期產出。 select from invoice_details where proje_ID = 6
invoice no invoice_amount invoice_Date received_amount
1004 245.00
預期產出。 select from invoice_details where proje_ID = 2
invoice no invoice_amount invoice_Date received_amount
1006 283.50
1007 0． 00 (becoz 1007 invoice is 不是 有 在 另一個 table called received_amount_details so show total sum 0。 00）。
預期產出。 select from invoice_details where proje_ID = 7
invoice no invoice_amount invoice_Date received_amount
1009 0.00
下面是我的查詢：
public function many($id) {
$sql = "SELECT invoice_details.Invoice_No, invoice_details.Invoice_Date, invoice_details.Invoice_amount,
SUM(received_amount_details.received_amount) as totalreceiptamt
從
(SELECT DISTINCT invoice_no, received_amount FROM received_amount_details) 作為 received_amount_details
INNER JOIN invoice_details ON received_amount_details.invoice_no = invoice_details.invoice_No
where invoice_details.proje_ID='7' 。
group by invoice_details.invoice_No"。
return $this->db->query($sql)->result（ ）。
}
目前我得到一個空的結果，如果我選擇發票細節，其中proje_ID='7' 我必須在我的php代碼中改變什么？
當我選擇一張發票（發票號碼：1004）時，它將顯示所有的發票細節和總的收款金額（205.00 40.00）。但如果選擇發票（發票號碼：1007），我得到空資料，因為這個1007的發票號碼在第二個表中不存在。如果它不在receive_amount_details表中，只需回傳發票細節和receive_amount總額=0.00。（我想顯示專案ID=2和receive_amount總額=0.00的發票細節。
uj5u.com熱心網友回復：
在SQL查詢中添加IF條件，就像這樣
。SELECT invoice_details.invoice_no,
invoice_details.invoice_date,
invoice_details.invoice_amount。
SUM(IF(received_amount_details.received_amount IS NULL)
OR received_amount_details.received_amount = ', 0.0,.
received_amount_details.received_amount)) AS totalreceiptamt
FROM (SELECT DISTINCT invoice_no,
收到的金額
FROM received_amount_details) AS received_amount_details
join invoice_details
ON received_amount_details.invoice_no =
發票_details.invoice_no
WHERE invoice_details.proje_id = '7'。
GROUP BY invoice_details.invoice_no
這將增加0.0，因為默認值不是 received_amount_details.received_amount IS NULL或空白
。另外，我們正在做右鍵連接，以便從發票_細節頁面獲得所有的值。
你可以在這里了解更多關于MYSQL中的IF條件，
。https://www.w3schools.com/mysql/func_mysql_if.asp https://dev.mysql.com/doc/refman/8.0/en/if.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/311031.html
標籤：
上一篇：同名的多個MySQL鍵的影響