求一条SQL语句

IssacChow 2018-12-23 08:18:42
有表结构如下

--日期 类型 金额
--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月)
...全文
82 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
IssacChow 2018-12-23
  • 打赏
  • 举报
回复
引用 2 楼 Dear SQL 的回复:
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 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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧