這是表記錄：
id date type amount
1 2020-08-03 income 88
2 2020-09-11 spending -120
3 2020-09-16 income 200
4 2020-11-05 income 95
5 2020-11-30 spending 35
如何獲得年度的月度統計資料？
例如 ：
{
"2021-11": {income: 500, spending: -800}
"2021-10": {income: 200, spending: -500}
"2021-09": {income: 800, spending: -300}
"2021-08": {income: 900, spending: -200}
}
ps: Sqlite
uj5u.com熱心網友回復：
您可以在 sum group 函式中使用 case 陳述句來確定它是收入還是支出行
select strftime('%Y-%m', date_col),
sum(case when type = 'income' then amount else 0 end) income,
sum(case when type = 'spending' then amount else 0 end) spending
from test_table
group by strftime('%Y-%m', date_col);
uj5u.com熱心網友回復：
你需要使用group by. 像這樣的東西：
SELECT COUNT(id), date, type, amount
FROM table
GROUP BY date;
如果你有日期問題，那么你需要：
GROUP BY CAST(date AS DATE)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/324355.html