按月分类，进行汇总

zpcoder 2009-01-09 10:06:19 
--表 tb_Records

cusId amount currentbalance createddate

--------------------------------------------

/*

cusId: 客户的ID号(与表tb_Customer.cusId 相对应)

amount：汇入或支付的金额(若为正表示客户的余额增多,负则表示减少)

currentbalance：汇入或支出后的余额 (当前行的currentbalance总是为上一行的currentbalance+当前后之后的值)

createddate：该条记录创建的时间(值总是getdate())

*/





/*需求：---------------

根据createddate字段,统计2007-12-26 到 2008-12-25 这一个中每一个月的欠款总数,和一整个的总和



*********重要说明：***************************************************



从Jan到Dec 的每一个月份的起始时间是从上一个月的26日到该月的25日记为一个月.

即 2007-12-26日就应该是2008年1月(Jan),2008-1-24和2008-1-25都是2008-1月

而 2008-1-26 就应该是 2008-2月(Feb)，依些类推 2008-11-26~2008-12-25记为2008-12(Dec)



以前在做统计时，有位大侠教我用了

SELECT  sum(quantity) as 'quantity',

	(CASE WHEN datepart(dd, OrderDate) > 25 THEN dateadd(dd, 8, OrderDate) ELSE OrderDate END) AS OrderDate, 

FROM tb_SaleRecords ……

后用发现有问题，统计的结果(数量)是对的。但日期不对.比如说明明是 2008-11-29 买了 100个,

结果它显示的时间是2008-12-07买的。



而我们系统是的。时间不变,只是这个销售量要记到Feb的销售总量中去







/*另一个重要说明：

上月结帐欠款截止本月5日尚未回收部分为欠款。

例：若2月份(2008-1-26 到 2008-2-25) 统计时的欠款为 2000元

在3月5日前又汇入一1500元，那么他2月欠款就为500,其它依此类推。



如果月统计出来如果不欠款(欠款为0或有多的余额)全记为 0

*/



*********************************************************************

统计结果应这样：



cusId	Jan Feb Mar Apr May …… Dec Total

---------------------------------------------

1011    100 50  50  100 0   …… 0   总和

*/
...全文
301 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
QQHRSWB 2011-11-09
  • 打赏
  • 举报
 回复
我也在找这方面的资料，
ye_zhi_gen_bo_luo_mi 2009-02-09
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 yangsnow_rain_wind 的回复:]
SQL code
楼主的意思是不是这样

SELECT 'quantity' = sum(quantity) as ,
'欠款' =
( case when
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0
then 0
else
(select sum(amount…
[/Quote]

怎么case 和else 语句是一样的??
case when
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0
then 0
else
(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0
肥龙上天 2009-02-08
  • 打赏
  • 举报
 回复 


楼主的意思是不是这样



SELECT  'quantity' = sum(quantity) as ,

	'欠款' = 

		(  case when

				 (select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0

				then 0

			else

				(select sum(amount) - sum(currentbalance) from tb_SaleRecords where orderDate between convert(nvarchar(7),dateadd(mm,-1,orderdate),120)+'-26' and convert(nvarchar(7,orderdate,120)+'-25')) > 0

		)

FROM tb_SaleRecords
bad4you51 2009-02-08
  • 打赏
  • 举报
 回复
关注
ws_hgo 2009-01-14
  • 打赏
  • 举报
 回复
很复杂
关注下..
zpcoder 2009-01-14
  • 打赏
  • 举报
 回复


我把要求重新整理了一下。请看

http://www.chenminghuaxia.cn/demo.html
zpcoder 2009-01-14
  • 打赏
  • 举报
 回复

我反要求重新整理了一下。请看

http://www.chenminghuaxia.cn/demo.html
dawugui 2009-01-13
  • 打赏
  • 举报
 回复
将上面的AS '1月' ... AS '12月'转换为英文的即可.
dawugui 2009-01-13
  • 打赏
  • 举报
 回复
[Quote=引用楼主 zpcoder 的帖子:]
--表 tb_Records
cusId amount currentbalance createddate
--------------------------------------------
/*
cusId: 客户的ID号(与表tb_Customer.cusId 相对应)
amount：汇入或支付的金额(若为正表示客户的余额增多,负则表示减少)
currentbalance：汇入或支出后的余额 (当前行的currentbalance总是为上一行的currentbalance+当前后之后的值)
createddate：该条记录创建的时间(值总是getdate())
*/


/*需求：---------------
根据createddate字段,统计2007-12-26 到 2008-12-25 这一个中每一个月的欠款总数,和一整个的总和

*********重要说明：***************************************************

从Jan到Dec 的每一个月份的起始时间是从上一个月的26日到该月的25日记为一个月.
即 2007-12-26日就应该是2008年1月(Jan),2008-1-24和2008-1-25都是2008-1月
而 2008-1-26 就应该是 2008-2月(Feb)，依些类推 2008-11-26~2008-12-25记为2008-12(Dec)

以前在做统计时，有位大侠教我用了
SELECT sum(quantity) as 'quantity',
(CASE WHEN datepart(dd, OrderDate) > 25 THEN dateadd(dd, 8, OrderDate) ELSE OrderDate END) AS OrderDate,
FROM tb_SaleRecords ……
后用发现有问题，统计的结果(数量)是对的。但日期不对.比如说明明是 2008-11-29 买了 100个,
结果它显示的时间是2008-12-07买的。

而我们系统是的。时间不变,只是这个销售量要记到Feb的销售总量中去



/*另一个重要说明：
上月结帐欠款截止本月5日尚未回收部分为欠款。
例：若2月份(2008-1-26 到 2008-2-25) 统计时的欠款为 2000元
在3月5日前又汇入一1500元，那么他2月欠款就为500,其它依此类推。

如果月统计出来如果不欠款(欠款为0或有多的余额)全记为 0
*/

*********************************************************************
统计结果应这样：

cusId Jan Feb Mar Apr May …… Dec Total
---------------------------------------------
1011 100 50 50 100 0 …… 0 总和
*/
[/Quote]


请给出表结构,测试数据,相关算法和需要的结果.谢谢!


如果是行列转换,你这个可以用静态的.参考:
/*

按月进行行列转换并加合计(2007-11-19于海南三亚)



例如有表tb某些人每月消费数据如下：

id data month

001 11 1

001 12 2

001 13 3

001 14 4

001 15 5

001 16 6

001 17 7

001 18 8

001 19 9

001 110 10

001 111 11

001 112 12

002 21 1

002 22 2

002 23 3

002 24 4

002 25 5

002 26 6

002 27 7

002 28 8

002 29 9

002 210 10

002 211 11

002 212 12

要实现如下结果： 

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月

001  11  12  13  14  15  16  17  18  19  110  111  112

002  21  22  23  24  25   26 27  28  29  210  211  212

*/



create table tb

(

  id    char(3),

  data  int,

  month int

)

insert into tb(id,data,month) values('001',11,1)

insert into tb(id,data,month) values('001',12,2)

insert into tb(id,data,month) values('001',13,3)

insert into tb(id,data,month) values('001',14,4)

insert into tb(id,data,month) values('001',15,5)

insert into tb(id,data,month) values('001',16,6)

insert into tb(id,data,month) values('001',17,7)

insert into tb(id,data,month) values('001',18,8)

insert into tb(id,data,month) values('001',19,9)

insert into tb(id,data,month) values('001',110,10)

insert into tb(id,data,month) values('001',111,11)

insert into tb(id,data,month) values('001',112,12)

insert into tb(id,data,month) values('002',21,1)

insert into tb(id,data,month) values('002',22,2)

insert into tb(id,data,month) values('002',23,3)

insert into tb(id,data,month) values('002',24,4)

insert into tb(id,data,month) values('002',25,5)

insert into tb(id,data,month) values('002',26,6)

insert into tb(id,data,month) values('002',27,7)

insert into tb(id,data,month) values('002',28,8)

insert into tb(id,data,month) values('002',29,9)

insert into tb(id,data,month) values('002',210,10)

insert into tb(id,data,month) values('002',211,11)

insert into tb(id,data,month) values('002',212,12)

go



SELECT id as '人员' , 

  SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,

  SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,

  SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,

  SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,

  SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,

  SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,

  SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,

  SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,

  SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,

  SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,

  SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,

  SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' 

FROM tb

GROUP BY ID 



drop table tb



/*

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月

---- --- --- --- --- --- --- --- --- --- ---- ---- ----

001  11  12  13  14  15  16  17  18  19  110  111  112

002  21  22  23  24  25  26  27  28  29  210  211  212



（所影响的行数为 2 行）

*/



--------------------------------------------------------------

/*

合计每个人每年的数据

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计

001  11  12  13  14  15  16  17  18  19  110  111  112  468

002  21  22  23  24  25   26 27  28  29  210  211  212  858

*/



SELECT id as '人员' , 

SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,

SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,

SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,

SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,

SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,

SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,

SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,

SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,

SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,

SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,

SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,

SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' ,

SUM(data) as '合计'

FROM tb

GROUP BY ID 



/*

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计

---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ----

001  11  12  13  14  15  16  17  18  19  110  111  112  468

002  21  22  23  24  25  26  27  28  29  210  211  212  858

*/

shineran 2009-01-09
  • 打赏
  • 举报
 回复
应该是类似这种结果：




SELECT A.cusId,CONVERT(VARCHAR(7),A.createddate,102) AS mon,

(

SELECT CASE WHEN SUM(amount)>=0 THEN 0 ELSE SUM(amount) END FROM tb_Records

WHERE createddate <DATEADD(M,1,CONVERT(VARCHAR(7),A.createddate,102)+'-5')

) AS 欠款

FROM tb_Records A 

GROUP BY 

A.cusId,CONVERT(VARCHAR(7),A.createddate,102)
zpcoder 2009-01-09
  • 打赏
  • 举报
 回复
这个帖子可以不显示日期，只要把结果算出来就可以了。
显示日期的是以前的一个(那以个要显示日期的，数据是对的，日期不对.先不说这个日期的问题.
只要满足第二个说明里的条件就可以了：
-------------------------------------
上月结帐欠款截止本月5日尚未回收部分为欠款。
例：若2月份(2008-1-26 到 2008-2-25) 统计时的欠款为 2000元
在3月5日前又汇入一1500元，那么他2月欠款就为500,其它依此类推。

如果月统计出来如果不欠款(欠款为0或有多的余额)全记为 0
-----------------------------------------------------
子陌红尘 2009-01-09
  • 打赏
  • 举报
 回复
[Quote=引用楼主 zpcoder 的帖子:]
统计的结果(数量)是对的。但日期不对.比如说明明是 2008-11-29 买了 100个,
结果它显示的时间是2008-12-07买的。

而我们系统是的。时间不变,只是这个销售量要记到Feb的销售总量中去
[/Quote]

只是一个查询中的处理，目的仅仅是在统计结果中将25日之后的数据归类到下一个月；因此，在统计的SQL里不会显示购买日期吧？
csdyyr 2009-01-09
  • 打赏
  • 举报
 回复 
--把26及之后的日期转为下一个月

SELECT  sum(quantity) as 'quantity',

    (CASE WHEN datepart(dd, OrderDate) > 25 THEN dateadd(mm, 1, OrderDate) ELSE OrderDate END) AS OrderDate, 

FROM tb_SaleRecords ……
wzy_love_sly 2009-01-09
  • 打赏
  • 举报
 回复
模拟一个包含所有情况的例子，帖上要的结果，这样比较好懂
Excel数据管理系列课程Execl排序与分类汇总
要使制作或设计的工作表更加具有可读和容易分析，可以通过一些常用的管理来实现，包括排序、分类汇总和筛选等。本课程将介绍相关的知识和技巧。
excel按季度分类汇总_Excel 分类汇总按年、月和周且用数据透视表与公式实现，并把汇总结果提取出来...
在 Excel 中，按年、月、日和周分类汇总用数据透视表比较简单方便，进行简单的设置就可以返回它们的汇总结果；按年、月和日汇总可以直接显示它们，但按周汇总只能用日期表示周；而用公式汇总可以实现把属于每周的日期转为第几周。用公式实现按年、月和周分类汇总，主要用 Sum、SumIfs、SumProduct、Value、WeekNum、Date、Row 和 Text 等函数，例如按年和月汇总用前四个函数...
excel按季度分类汇总_按部门对Excel表格中数据进行分类汇总的方法
分类汇总是Excel的一项重要功能，它能快速以某一个字段为分类项，对数据列表中其他字段的数值进行统计计算。本文以在表格中按照部门来统计数据总和为例介绍Excel表格中数据进行分类汇总的方法的操作方法。1、启动Excel并打开工作表，选择汇总时的分类字段所在的单元格区域，在“开始”选项卡的“编辑”组中单击“排序和筛选”按钮，在打开的菜单中选择“降序”命令对其进行排序操作，如图1所示。图1　对数据进行...
计算机分类汇总前必须进行什么操作,分类汇总前必须先进行什么操作
分类汇总前，必须先按分类字段进行“排序”操作，以确保分类依据的类别处于连续的位置，之间不出现间隔。排序方法：选中所有数据，点击“数据”-“排序”；然后设置“主要关键字”、“排序依据”、“次序”；最后点击“确定”即可。本文操作环境：windows7系统、Microsoft Office Excel2016版本、Dell G3电脑。Excel中如果想对某类数据进行汇总，除了使用数据透视表之外，还可以使...
java性别分类汇总,excel表格 男女数据分开-Excel按性别进行分类汇总
excel 分类汇总问题 按“职称”和“性别”进行分类，分...这个用数据透视就好，你也可以有汇总的SUBTOTAL例：=SUBTOTAL(3,A1:A18)表示的是A1到A18相同的汇总个数来实现的，EXCEL表格里如何统计男、女的人数分别为多少啊？这个用数据透视就好，你也可以有汇总的SUBTOTAL例：=SUBTOTAL(3,A1:A18)表示的是A1到A18相同的汇总个数来实现的，excel...
MS-SQL Server

34,594

社区成员

254,589

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章