我剛開始使用 sqlite3 并堅持使用（聯合）和組合多個查詢，是否有正確的方法來做到這一點
我正在處理以下資料
sql table =>
account |ledger_num | date_from | interest | name | loan | loan_type
____________|___________|_______________|_______________|___________|_________|____________________
Book_1 | HP2269 | 2017-03-31 | 15 | Samuel | 120 | type_a
Book_3 | HP2236 | 2020-03-31 | -20 | bob | 190 | type_a
Book_1 | HP2269 | 2019-04-01 | 250 | Samuel | 1500 | type_b
Book_3 | HP2236 | 2019-04-01 | -3 | bob | 36 | type_b
Book_2 | L01 | 2020-03-31 | 16 | josh | 95 | type_a
Book_1 | HP2269 | 2022-04-01 | 400 | Samuel | 4050 | type_c
這就是我想要做的
var sql_1 = `SELECT SUM(loan) ,SUM(interest) FROM allYearAllAcc WHERE (account = 'Book_1' AND date_from > '2018-03-31')`
var sql_2 = `SELECT SUM(interest) FROM allYearAllAcc WHERE (account = 'Book_2' AND date_from > '2017-04-01' AND interest < 0)`
var sql_3 = `SELECT SUM(loan) FROM allYearAllAcc WHERE (account = 'Book_3' AND date_from < '2022-04-01')`
temp_db.all(`` sql_1 `UNION` sql_2 `UNION` sql_3 ``,(err,data)=>{
console.log(data)
})
這顯然不起作用我希望它顯示為一個 json 陣列，我必須在我的代碼中使用它
uj5u.com熱心網友回復：
使用條件聚合和表上的單個查詢：
SELECT
SUM(CASE WHEN account = 'Book_1' AND date_from > '2018-03-31'
THEN loan ELSE 0 END) AS loan1,
SUM(CASE WHEN account = 'Book_1' AND date_from > '2018-03-31'
THEN interest ELSE 0 END) AS interest1,
SUM(CASE WHEN account = 'Book_2' AND date_from > '2017-04-01' AND interest < 0
THEN interest ELSE 0 END) AS interest2,
SUM(CASE WHEN account = 'Book_3' AND date_from < '2022-04-01'
THEN loan ELSE 0 END) AS loan2
FROM allYearAllAcc;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/442560.html
標籤：javascript 节点.js 数据库 sqlite 节点-sqlite3
下一篇：內部連接相關表時如何保留表名