分类求和

[twtiqfn]

表：a
began（起始日期） end（终止日期） js（基数） zjs（总基数）
1994-1-1 1994-12-1 100.00 1200.00
1995-1-1 1995-5-1 100.00 500.00
1995-6-1 1998-3-1 100.00 34000.00
我想根据began和end字段按年分组对zjs求和:表中的关系是：
js*(end -began)=zjs 其中，end -began是两个字段日期间的月数，
得到以下的结果：
year(年份) zjs总基数
1994 1200
1995 500
1995 700
1996 1200
1997 1200
1998 300

其中：结果表中的3到6行，是由表中的第3条记录分解出来的，因为第3条记录包含了1995年7个月（zjs=7*100，1996年和1997年分别12个月(zjs=12*100)，1998年3个月(zjs=3*100)

想在查询分析器中运行命令，得出以下结果,求教啊，

[还在加载中灬]

SELECT YEAR(DATEADD(MONTH,T2.number,T1.began))AS[year]
	,SUM(T1.js)zjs
FROM a T1
	JOIN master..spt_values T2 ON T2.number<=DATEDIFF(MONTH,T1.began,T1.[end])
WHERE T2.type='P'
GROUP BY YEAR(DATEADD(MONTH,T2.number,T1.began)),T1.[began]
ORDER BY 1

[zhaowei303]

用一条语句不好实现的，我想到的时用游标来遍历每一条记录，然后进行分解，再把想要的结果存入临时表。

[道玄希言]

;with cte
as(
select began, [end], js from tbl7
union all
select dateadd(MONTH, 1, began), [end],js from cte as a
where not exists(select began, [end],js from tbl7 b
where b.began=DATEADD(MONTH, 1, a.began)
)
and a.began < [end]
)
select YEAR([began]) as '年份', sum(js) as '总基数' from cte group by YEAR([began]), [end]

有最大递归 100 限制....

[twtiqfn]

第1行： ’；’附近有语法错误，怎么办

[道玄希言]

引用 4 楼 twtiqfn 的回复:

第1行： ’；’附近有语法错误，怎么办

你搞一个全角的分号想干啥? ’；’ 和 ';' 不一样了

[Tiger_Zhao]

WITH /* 测试数据
a(began,[end],js,zjs) AS (
	SELECT '1994-01-01','1994-12-01',100.00,1200.00 UNION ALL
	SELECT '1995-01-01','1995-05-01',100.00,500.00 UNION ALL
	SELECT '1995-06-01','1998-03-01',100.00,34000.00
), */
/* 准备一个年份和起至月份的辅助表，方便查询
ym (year,began,[end])AS (
    SELECT 1994,'1994-01-01','1994-12-01' UNION ALL
    SELECT 1995,'1995-01-01','1995-12-01' UNION ALL
    SELECT 1996,'1996-01-01','1996-12-01' UNION ALL
    SELECT 1997,'1997-01-01','1997-12-01' UNION ALL
    SELECT 1998,'1998-01-01','1998-12-01'
), */
t AS (
    SELECT ym.[year],
           CASE WHEN a.began > ym.began THEN
                a.began
           ELSE
                ym.began
           END began,
           CASE WHEN a.[end] < ym.[end] THEN
                a.[end]
           ELSE
                ym.[end]
           END [end]
           , a.js ,a.zjs -- 切割后的起至月份，验证用，可以删除
      FROM ym
      JOIN a
        ON (a.began BETWEEN ym.began AND ym.[end])
        OR (a.[end] BETWEEN ym.began AND ym.[end])
        OR (ym.began BETWEEN a.began AND a.[end])
)
SELECT [year],
       (DATEDIFF(month,began,[end])+1)*js zjs,
       began,
       [end] 
  FROM t

       year                                     zjs began      end
----------- --------------------------------------- ---------- ----------
       1994                                 1200.00 1994-01-01 1994-12-01
       1995                                  500.00 1995-01-01 1995-05-01
       1995                                  700.00 1995-06-01 1995-12-01
       1996                                 1200.00 1996-01-01 1996-12-01
       1997                                 1200.00 1997-01-01 1997-12-01
       1998                                  300.00 1998-01-01 1998-03-01

[twtiqfn]

开始的地方你作了一个测试表a,而我这只是用几条记录举了个例子，实际上表中的记录有很多条，不可能所所有的记录放进测试中的，怎么把上述程序优化一下啊

[twtiqfn]

引用 1 楼 ky_min 的回复:

SELECT YEAR(DATEADD(MONTH,T2.number,T1.began))AS[year]
	,SUM(T1.js)zjs
FROM a T1
	JOIN master..spt_values T2 ON T2.number<=DATEDIFF(MONTH,T1.began,T1.[end])
WHERE T2.type='P'
GROUP BY YEAR(DATEADD(MONTH,T2.number,T1.began)),T1.[began]
ORDER BY 1

程序很完美，谢谢你，方便告诉我你的联系点方式吗。信箱，QQ都行，谢谢