- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 用一条sql语句如何统计出全年的租金? (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=126401)
-- 发布时间：2018/10/20 16:13:00
-- 用一条sql语句如何统计出全年的租金?
| 合同号码 | 合同起始日期 | 合同截止日期 | 月租金 |
| 201801 | 2018-01-01 | 2019-12-31 | 100 |
| 201802 | 2017-11-01 | 2018-10-31 | 200 |
| 201803 | 2017-01-01 | 2018-03-31 | 300 |
| 201803 | 2018-04-04 | 2018-11-31 | 400 |
| 2018年1-12月,月租金合计数 | |||
| 合同号码 | 月租金合计 | 备注 | |
| 201801 | 1200 | 12*100 | |
| 201802 | 2000 | 10*100 | |
| 201803 | 4100 | 3*300+8*400 | |
如何用一条sql语句统计出每个合同号码1-12月的月租金合计?
-- 发布时间：2018/10/20 16:23:00
--
试试
select 合同号码,sum(月租金) from (select 合同号码, DateDiff(m, 合同起始日期, 合同截止日期) * 月租金 as 月租金 from 表A) as a group by 合同号码
-- 发布时间：2018/10/20 21:25:00
--
不对，你这是整个表统计，这个不难。
现在难点就在于： 合同的统计时段被限制在2018-1-1 到2018-12=31日，
而有些合同 起始日期是在2018-1-1 之前，有些截止日期是在2018-12-31 之后，而有些合同分成了2段。这才是难点
-- 发布时间：2018/10/21 9:34:00
--
不难啊，你自定义统计那一年，自己选择不就行了，如果只统计当年，直接获取当面的日期，取年不就可以了啊。
-- 发布时间：2018/10/21 12:44:00
--
以下是引用sloyy在2018/10/20 21:25:00的发言：
不对，你这是整个表统计，这个不难。
不对，你这是整个表统计，这个不难。
现在难点就在于： 合同的统计时段被限制在2018-1-1 到2018-12=31日，
而有些合同 起始日期是在2018-1-1 之前，有些截止日期是在2018-12-31 之后，而有些合同分成了2段。这才是难点
1、假定统计2018年的数据；
2、2018年1月1日大于起始日期，取2018-1-1，否则，取起始日期；
3、2019年1月1日小于结束日期，取2019-1-1，否则，取结束日期；
4、从2、3中得到日期，直接相减，计算月数或者日数，乘以每月、每日的租金即可得到；
5、最后把所有租金sum起来。
-- 发布时间：2018/10/21 13:47:00
--
我采用了一个办法: 全年12个月 减去1月后起租的月份,减去12月前止租的月份
s elect 合同号码,月租金,(12+sum(case when 合同起始日期>\'2018-01-01\' then DateDiff(m,合同起始日期, \'2018-01-01\') else 0 end )+sum(case when 合同截止日期<\'2018-12-31\' then DateDiff(m, \'2018-12-31\',合同截止日期) else 0 end ))*月租金 as 月租金合计 from {租金明细} group by 合同号码,月租金得出了结果:
| 合同号码 | 月租金 | 月租金合计 |
| 201801 | 100 | 1200 |
| 201802 | 200 | 2000 |
| 201803 | 300 | 900 |
| 201803 | 400 | 3200 |
还要再做一次分组合计,才能得出正确的结果 上面这个sql语句还要怎么改,才能一次就达到目的? 也就是说二次分组汇总
[此贴子已经被作者于2018/10/21 13:59:41编辑过]
-- 发布时间：2018/10/21 14:52:00
--
再写一个select语句即可，比如
select 合同号码, sum(月租金) as 总租金 from (本来的sql语句) as a group by 合同号码
-- 发布时间：2018/10/21 15:09:00
--
s elect 合同号码, sum(月租金合计) as 总租金 from (s elect 合同号码,月租金,(12+sum(case when 合同起始日期>\'2018-01-01\' then DateDiff(m,合同起始日期, \'2018-01-01\') else 0 end )+sum(case when 合同截止日期<\'2018-12-31\' then DateDiff(m, \'2018-12-31\',合同截止日期) else 0 end ))*月租金 as 月租金合计 from {租金明细} a group by 合同号码,月租金) as a group by 合同号码
| 合同号码 | 月租金合计 |
| 201801 | 1200 |
| 201802 | 2000 |
| 201803 | 4100 |
搞定!!! 谢谢大家 
