mysql - 使用 Group By 查询具有多个条件的多列

Question

我有 table

account_no | name | date        | amount 
1101       |   A  |  2018-11-20 |  50
1101       |   A  |  2018-11-20 |  20
1102       |   B  |  2018-11-20 |  30    
1101       |   A  |  2018-11-19 |  100
1101       |   A  |  2018-11-18 |  80
1102       |   B  |  2018-11-19 |  70

我想要以下输出

account_no | name  | group(today) | group(month)
1101       |   A   |     70       |  250 
1102       |   B   |     30       |  100  

我不是在同一行中得到今天的总和或一个月的总和。到目前为止我尝试了什么

select account_no, sum(today) from account group by account_no having date = '<today>'

select account_no, sum(today) from account group by account_no having date between '<firstDay>' and '<today>'

我可以考虑使用 UNION ALL 但给出重复的行。

Answer 1

您可以尝试使用条件聚合

select account_no,name, sum(case when yourdate='<today>' then amount end) as sumoftoday,
sum(amount) for sumofmonth
from tablename
where yourdate between '<firstDay>' and '<today>'
group by account_no,name