按照日期倒序显示每天的数据统计
页面效果
image.png
sql结果
image.png
一条sql
select
CONVERT(varchar(100), r.ReDate, 23) times,
sum((r.Amount - r.SyAmount) - j.Loanamount) as shouyi,
cast((case when sum(j.Loanamount)=0 then 0 else sum(r.Amount - r.SyAmount) / sum(j.Loanamount) end) as DECIMAL(3,2)) * 100 as benjinshouyi,
count(r.Id) as fangkuandingdanshu,
count(case when r.State=3 then r.Id else null end) as huankuandingdanshu,
sum(j.Loanamount) as Loanamount,
sum(r.ReAmount) as ReAmount,
sum(r.SyReAmount) as SyReAmount,
sum(r.ReAmount-r.SyReAmount) as YhReAmount,
sum(r.InterestAmount-r.SyInterestAmount) as YhInterestAmount,
sum(r.Fine-r.SyFine) as YhFine,
sum(r.Amount-r.SyAmount) as YhAmount,
cast(sum(r.Amount-r.SyAmount)/sum(r.Amount) as DECIMAL(3,2)) * 100 as huankuanlv
from SysRePayment as r left join SysJkApply as j on r.OrderNO=j.OrderNO
where r.CompanyId=13
and (CONVERT(varchar(100), r.ReDate, 23) <= CONVERT(varchar(100), GETDATE(), 23))
and r.ReDate BETWEEN '2022-03-01' and '2022-03-31'
and r.ProjectId = 14
GROUP BY CONVERT(varchar(100), r.ReDate, 23)
ORDER BY CONVERT(varchar(100), r.ReDate, 23) desc
做数据统计的可以参考下数据很快就统计出来了，库里有几十万的数据
也可以写成存储过程
按月显示统计数据
sql结果
image.png
sql
select
DATEname(year, r.ReDate)+'-'+DATEname(month, r.ReDate) times,
sum((r.Amount - r.SyAmount) - j.Loanamount) as shouyi,
cast((case when sum(j.Loanamount)=0 then 0 else sum(r.Amount - r.SyAmount) / sum(j.Loanamount) end) as DECIMAL(3,2)) * 100 as benjinshouyi,
count(r.Id) as zongdanshu,
sum(case when r.State=3 then r.Amount else null end) as zonghuankuanjine,
sum(j.Loanamount) as zongfangkuanjine,
sum(j.Amount) as hetongjine,
(case when count(r.Id)=0 then 0 else sum(j.Amount)/count(r.Id) end) as kedanjia,
(case when count(r.Id)=0 then 0 else sum(r.ServiceAmount)/count(r.Id) end) as shouxufeibilijunzhi
from SysRePayment as r left join SysJkApply as j on r.OrderNO=j.OrderNO
where r.CompanyId=13
and (CONVERT(varchar(100), r.ReDate, 23) <= CONVERT(varchar(100), GETDATE(), 23))
and r.ProjectId = 14
GROUP BY DATEname(year, r.ReDate)+'-'+DATEname(month, r.ReDate) ORDER BY DATEname(year, r.ReDate)+'-'+DATEname(month, r.ReDate) desc
