我可以将两个SQLite SELECT语句合并为一个吗？

I have a SQLite table called posts. An example is shown below. I would like to calculate the monthly income and expenses.

我有一个名为posts的SQLite表。一个例子如下所示。我想计算每月的收入和支出。

accId       date        text                      amount      balance   
----------  ----------  ------------------------  ----------  ----------
1           2008-03-25  Ex1                       -64.9       3747.56  
1           2008-03-25  Shop2                     -91.85      3655.71  
1           2008-03-26  Benny's                   -100.0      3555.71

For the income I have this query:

对于收入我有这个查询:

SELECT SUBSTR(date, 0,7) "month", total(amount) "income" FROM posts
WHERE amount > 0 GROUP BY month ORDER BY date;

It works fine:

它工作正常:

month       income    
----------  ----------
2007-05     4877.0   
2007-06     8750.5   
2007-07     8471.0   
2007-08     5503.0

Now I need the expenses and I could of cause just repeat the first statement with the condition amount < 0, but I am wondering if there is an elegant way to get both income and expenses in one query?

现在我需要费用,因为我可以重复条件金额<0的第一个语句,但我想知道是否有一种优雅的方法来在一个查询中获得收入和费用?

3 个解决方案

#1

Try something like this

尝试这样的事情

select substr(date, 0,7) "Month",
       total(case when a > 0 then a else 0 end) "Income",
       total(case when a < 0 then a else 0 end) "Expenses"
from posts
group by month

#2

Look into the UNION statement (bottom of the link). This will let you combine the results of two queries, generally in the form:

查看UNION语句(链接的底部)。这将允许您将两个查询的结果组合在一起,通常采用以下形式:

<SELECT_STATEMENT_1> UNION <SELECT_STATEMENT_2>

#3

Not sure if SQL Lite supports CASE statements, but if it does you could do something like this.

不确定SQL Lite是否支持CASE语句,但如果确实如此,您可以执行类似的操作。

SELECT SUBSTR(date, 0,7) "month"
, total(CASE WHEN Amount > 0 THEN Amount ELSE 0 END) "income" 
, -1 * total(CASE WHEN Amount < 0 THEN Amount ELSE 0 END) "expenses" 
FROM posts 
GROUP BY month 
ORDER BY date;

#1