34,837
社区成员
发帖
与我相关
我的任务
分享求一条SQL语句
有表结构如下
需要的结果如下
即：借还款金额=当月金额之和
累计借还款金额=年内借还款之和（如：1月=1月，2月=1月+2月，3月=1月+2月+3月）
--日期 类型 金额
--2018-01-01 借款 100
--2018-01-31 借款 -50
--2018-01-20 还款 50
--2018-01-23 还款 -20
--2018-02-01 借款 100
--2018-02-31 借款 -50
--2018-02-20 还款 50
--2018-02-23 还款 -20
--2018-03-01 借款 100
--2018-03-31 借款 -50
--2018-03-20 还款 50
--2018-03-23 还款 -20
需要的结果如下
--月份 借款金额 还款金额 累计借款 累计还款
--2018-01 50 30 50 30
--2018-02 50 30 100 60
--2018-03 50 30 150 90
即：借还款金额=当月金额之和
累计借还款金额=年内借还款之和（如：1月=1月，2月=1月+2月，3月=1月+2月+3月）
...全文
请发表友善的回复…
发表回复
IssacChow 2018-12-23
- 打赏
- 举报
异曲同工，谢谢！
IssacChow 2018-12-23
- 打赏
- 举报
又写了一条
SELECT t.PERIOD,
t.plansAmount,
t.actualAmount,
SUM(t.plansAmount) OVER (ORDER BY t.PERIOD) AS sumPlansAmount,
SUM(t.actualAmount) OVER (ORDER BY t.PERIOD) AS sumActualAmount
FROM
(
SELECT pd.PERIOD,
SUM(CASE pd.STATISTICAL_METHOD WHEN '借款' THEN pd.AMOUNT_OF_MONEY ELSE 0 END ) plansAmount,
SUM(CASE pd.STATISTICAL_METHOD WHEN '还款' THEN pd.AMOUNT_OF_MONEY ELSE 0 END ) actualAmount
FROM dbo.FS_PROFIT_DATA pd
WHERE pd.DATA_STATUS = '1'
AND pd.PERIOD >= '2018-01-01'
AND pd.PERIOD <= '2018-12-31'
AND pd.ORGID = 'DY'
GROUP BY pd.PERIOD
) t;
Dear SQL(燊) 2018-12-23
- 打赏
- 举报
with list as(
select cast('2018-01-01' as datetime) as 日期,'借款' as 类型,cast(100 as float) as 金额 union all
select cast('2018-01-31' as datetime) as 日期,'借款' as 类型,cast( -50 as float) as 金额 union all
select cast('2018-01-20' as datetime) as 日期,'还款' as 类型,cast( 50 as float) as 金额 union all
select cast('2018-01-23' as datetime) as 日期,'还款' as 类型,cast( -20 as float) as 金额 union all
select cast('2018-02-01' as datetime) as 日期,'借款' as 类型,cast(100 as float) as 金额 union all
select cast('2018-02-28' as datetime) as 日期,'借款' as 类型,cast( -50 as float) as 金额 union all
select cast('2018-02-20' as datetime) as 日期,'还款' as 类型,cast( 50 as float) as 金额 union all
select cast('2018-02-23' as datetime) as 日期,'还款' as 类型,cast( -20 as float) as 金额 union all
select cast('2018-03-01' as datetime) as 日期,'借款' as 类型,cast(100 as float) as 金额 union all
select cast('2018-03-30' as datetime) as 日期,'借款' as 类型,cast( -50 as float) as 金额 union all
select cast('2018-03-20' as datetime) as 日期,'还款' as 类型,cast( 50 as float) as 金额 union all
select cast('2018-03-23' as datetime) as 日期,'还款' as 类型,cast( -20 as float) as 金额
),datas as(
select convert(varchar(7),日期,120) as 月份
,借款金额=sum(case when 类型='借款' then 金额 else 0 end)
,还款金额=sum(case when 类型='还款' then 金额 else 0 end)
from list
group by convert(varchar(7),日期,120)
)
select *,累计借款=sum(借款金额)over(order by 月份)
,累计还款=sum(还款金额)over(order by 月份)
from datas
月份 借款金额 还款金额 累计借款 累计还款
------- ---------------------- ---------------------- ---------------------- ----------------------
2018-01 50 30 50 30
2018-02 50 30 100 60
2018-03 50 30 150 90
IssacChow 2018-12-23
- 打赏
- 举报
写了一条，貌似太复杂了。
SELECT pd.PERIOD,
SUM(CASE pd.STATISTICAL_METHOD WHEN '借款' THEN pd.AMOUNT_OF_MONEY ELSE 0 END ) plansAmount,
SUM(CASE pd.STATISTICAL_METHOD WHEN '还款' THEN pd.AMOUNT_OF_MONEY ELSE 0 END ) actualAmount,
(select sum(AMOUNT_OF_MONEY) from FS_PROFIT_DATA b where b.PERIOD<= pd.PERIOD AND b.STATISTICAL_METHOD='借款' AND b.DATA_STATUS = '1' AND b.PERIOD >= '2018-01-01' AND b.PERIOD <= '2018-12-31' AND b.ORGID = 'DY') sumPlansAmount,
(select sum(AMOUNT_OF_MONEY) from FS_PROFIT_DATA c where c.PERIOD<= pd.PERIOD AND c.STATISTICAL_METHOD='还款' AND c.DATA_STATUS = '1' AND c.PERIOD >= '2018-01-01' AND c.PERIOD <= '2018-12-31' AND c.ORGID = 'DY') sumactualAmount
FROM dbo.FS_PROFIT_DATA pd
WHERE pd.DATA_STATUS = '1' AND pd.PERIOD >= '2018-01-01' AND pd.PERIOD <= '2018-12-31' AND pd.ORGID = 'DY'
GROUP BY pd.PERIOD
