所以我有一個包含 4 列的表，并且想對amountwhere isExpenseistrue和 where isExpenseis列的值求和false。我想減去這兩個值并回傳該總和。
除了單行查詢之外，我對 SQL 沒有太多經驗，所以我正在努力格式化它。
@Query("""
SELECT SUM (amount) AS INCOME FROM `transaction` WHERE isExpense = 0,
SELECT SUM (amount) AS EXPENSE FROM `transaction` WHERE isExpense = 1,
SUM (INCOME - EXPENSE) AS BALANCE
""")
fun getTotalBalance(): Flow<Double>?
如果所有其他方法都失敗了，我可以通過在我的表中創建更多列來解決這個問題。
uj5u.com熱心網友回復：
使用case 運算式進行條件聚合：
SELECT SUM(case when isExpense = 0 then amount else 0 end) AS INCOME,
SUM(case when isExpense = 1 then amount else 0 end) AS EXPENSE,
SUM(case when isExpense = 0 then amount
when isExpense = 1 then -amount
end) as BALANCE
FROM `transaction`
WHERE isExpense IN (0, 1) -- Not needed, but might speed things up if there
-- are other values than 0 and 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/399508.html
標籤：安卓 sql 科特林 android-room