我有一个名为“rentals”的表，其中存储如下数据：
id | rent_id | start_date | end_date | amount
---------------------------------------------
1 | 54 | 12-10-2019 | 26-10-2019| 100
2 | 54 | 13-10-2019 | 20-10-2019| 150
我期待什么？结果如下：
12-10-2019 , amount 100
from 13-10-2019 to 20-10-2019, amount 250
from 21-10-2019 to 26-10-2019, amount 100
基本上我想要每天的金额总和。但我也想计算“天数”。
所以预期的结果是：
id | rent_id | day | amount
---------------------------------------------
1 | 54 | 12-10-2019 | 100
2 | 54 | 13-10-2019 | 250
3 | 54 | 14-10-2019 | 250
等等...
我实际上正在运行以下sql：
select start_date, ( select sum(amount) from rentals as t2 where t2.start_date <= t1.start_date) as amount from rentals as t1 WHERE rent_id = 54 group by start_date
但结果并不像预期的那样...
我正在使用MySQL。
我认为这可能有帮助使用MSSQL
declare @startDate date ='11 Oct 2019'
declare @endDate date ='30 Oct 2019'
declare @rentId nvarchar(50)='54'
select d.Day,d.rent_id,isnull(d.amount,0) as amountPaid, amount = SUM(d.amount) OVER (ORDER BY d.Day) from (
select c.Day,c.id,case when rent_id is null then @rentId else rent_id end as rent_id,c.amount from (
select * from (
SELECT DATEADD(DAY,number+1,@startDate) [Day]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@startDate) < @endDate
) a left join (select * from rentals where rent_id=@rentId) b on a.[Day] = b.start_date) c) d
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
