我想得到這樣的結(jié)果result -------------------------------------------------------id | uuid | user_id |created_date | amount | name -------------------------------------------------------1 | ABC | 1 | 2019/5/1 | 5 | xa2 | PQR | 2 | 2019/5/5 | 150 | xb我嘗試使用的查詢SELECT(SELECT SUM(paid_amount) WHERE ID = t1.**HERE**) AS sub1,(t1.amount - sub1) AS sub2FROM invoice t1 CROSS JOINinvoice_paid t2;我的數(shù)據(jù)庫中的表結(jié)構(gòu)table invoice_paid ------------------------------------id | uuid | paid_date | paid_amount------------------------------------1 | ABC | 2019/5/1 | 152 | ABC | 2019/5/5 | 80 table invoice-------------------------------------------------------id | uuid | user_id |created_date | amount | name -------------------------------------------------------1 | ABC | 1 | 2019/5/1 | 100 | xa2 | PQR | 2 | 2019/5/5 | 150 | xb我只能使用 sum 1 條件，如 where id = 1 但我如何將選擇查詢中的此查詢與連接查詢結(jié)合起來。我使用 beego(golang)，MariaDB
1 回答
慕沐林林
TA貢獻(xiàn)2016條經(jīng)驗 獲得超9個贊
您可以使用此查詢。它將invoice表連接到派生表SUMs，其中包含每張發(fā)票支付的所有金額invoice_paid，從發(fā)票金額中減去總計以獲得未結(jié)金額：
SELECT i.id, i.uuid, i.user_id, i.created_date, i.amount - COALESCE(p.amount, 0) AS amount, i.name
FROM invoice i
LEFT JOIN (SELECT uuid, SUM(paid_amount) AS amount
? ? ? ? ? ?FROM invoice_paid
? ? ? ? ? ?GROUP BY uuid) p ON p.uuid = i.uuid
ORDER BY i.id
輸出：
id? uuid? ? user_id created_date? ? ? ? name? ? amount
1? ?ABC? ? ?1? ? ? ?2019-05-01 00:00:00 xa? ? ? 5
2? ?PQR? ? ?2? ? ? ?2019-05-05 00:00:00 xb? ? ? 150
- 1 回答
- 0 關(guān)注
- 152 瀏覽
添加回答
舉報
0/150
提交
取消