我在表上的查询需要以下条件:
表:
user_id || amount || date
1 || 10 || 2019-04-01
1 || 25 || 2019-04-02
3 || 25 || 2019-04-04
1 || 25 || 2019-04-03
期望结果:获取USER_ID,日期,日期,每月的金额(总计user_id的所有日期,一个月(
输入参数:user_id,日期,开始月的开始日期,每月结束日期。
ex:user_id = 1,date = 2019-04-02,start_date_of_month = 2019-04-01,end_date_of_month = 2019-04-04-30
user_id || amount_of_date || date || amount_of_month
1 || 25 || 2019-04-02 || 60
您可以在下面尝试 -
select user_id,
max(case when date=@inputdate then amount end) as amount_of_Date,
min(case when date=@inputdate then date end) as date,
sum(amount) as amount_of_month
from tablename
where user_id=1 and date between '2019-04-01' and '2019-04-30'
group by user_id