题目:已知表
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 | 50 | 250
1102 | B | 30 | 100
答案:设表为test,则有如下
select
account_no,
name,
t.tt,
m.tm
FROM
test
LEFT JOIN
(select
name as tname,sum(amount) as tt
from
test
where
date='2018-11-20'
GROUP BY
name
)t on t.tname=test.name
LEFT JOIN
(select
name as mname,sum(amount) as tm
from
test
GROUP BY DATE_FORMAT(date,'%Y%M'),name
)m on m.mname=test.name
group by
test.account_no,test.name