SQL2000 统计每周,每月,每季,每年的数据

原创 2011年09月11日 08:51:13

表order(订单表)

  1. order_id    client_id(客户ID)  order_time(订单时间)  
  2.    1               1             2007-1-5  
  3.    2               1             2007-1-7  
  4.    3               1             2007-6-5  
  5.    4               3             2007-2-5  
  6.    5               3             2007-2-18  

表item(明细表)

  1. item_id order_id(明细表) pro_id(产品ID)  pro_amount(数量)  pro_price(单价)  
  2.    1         1               1              10                 10.00  
  3.    2         1               3               5                 15.00  
  4.    3         2               1               5                 12.00  
  5.    4         3               2               10                8.00  
  6.    5         4               3                2                15.00  
  7.    6         5               2                6                 10.00  

如何汇总得到如下效果:
1.按年得到总金额
client_id   1月   2月   3月    4月  5月  6月 ...   12月
    1     235.00  0.00  0.00  0.00 0.00  80.00 ... 0.00
    3      0.00   90.00 0.00  0.00 0.00  0.00...   0.00
2.按周得到总金额:
client_id 周一  周二  ....周日
   1
   3
3.按月得到总金额:
client_id  1号  2号  3号.....31号
   1
   3
4.按季:
client_id 第一季度  第二季度  第二季度   第四季度
    1
    3
================================================================

数据准备:

 

  1. create table [order]  
  2. (  
  3. order_id int,  
  4. client_id int,  
  5. order_time datetime  
  6. )  
  7. create table item  
  8. (  
  9. item_idint,  
  10. order_id int,  
  11. pro_id int,  
  12. pro_amount int,  
  13. pro_price int  
  14. )  
  15. insert into [order]  
  16. select 1,1,'2007-1-5'  
  17. union all  
  18. select 2,1,'2007-1-7'  
  19. union all  
  20. select 3,1,'2007-6-5'  
  21. union all  
  22. select 4,3,'2007-2-5'  
  23. union all  
  24. select 5,3,'2007-2-18'  
  25. insert into item  
  26. select 1,1,1,10,10  
  27. union all  
  28. select 2,1,3,5,15  
  29. union all  
  30. select 3,2,1,5,12  
  31. union all  
  32. select 4,3,2,10,8  
  33. union all  
  34. select 5,4,3,2,15  
  35. union all  
  36. select 6,5,2,6,10  

按年统计:

 

  1. select client_id ,  
  2.   sum(case when datepart(month,order_time) = 1 then pro_amount*pro_price else 0 end'1月',  
  3.   sum(case when datepart(month,order_time) = 2 then pro_amount*pro_price else 0 end'2月',  
  4.   sum(case when datepart(month,order_time) = 3 then pro_amount*pro_price else 0 end'3月',  
  5.   sum(case when datepart(month,order_time) = 4 then pro_amount*pro_price else 0 end'4月',  
  6.   sum(case when datepart(month,order_time) = 5 then pro_amount*pro_price else 0 end'5月',  
  7.   sum(case when datepart(month,order_time) = 6 then pro_amount*pro_price else 0 end'6月',  
  8.   sum(case when datepart(month,order_time) = 7 then pro_amount*pro_price else 0 end'7月',  
  9.   sum(case when datepart(month,order_time) = 8 then pro_amount*pro_price else 0 end'8月',  
  10.   sum(case when datepart(month,order_time) = 9 then pro_amount*pro_price else 0 end'9月',  
  11.   sum(case when datepart(month,order_time) = 10 then pro_amount*pro_price else 0 end'10月',  
  12.   sum(case when datepart(month,order_time) = 11 then pro_amount*pro_price else 0 end'11月',  
  13.   sum(case when datepart(month,order_time) = 12 then pro_amount*pro_price else 0 end'12月'  
  14. from order,item where order.order_id = item.order_id  
  15. group by client_id  

按季度统计:

  1. select client_id ,  
  2.   sum(case when datepart(quarter,order_time) = 1 then pro_amount*pro_price else 0 end'第一季度',  
  3.   sum(case when datepart(quarter,order_time) = 2 then pro_amount*pro_price else 0 end'第二季度',  
  4.   sum(case when datepart(quarter,order_time) = 3 then pro_amount*pro_price else 0 end'第三季度',  
  5.   sum(case when datepart(quarter,order_time) = 4 then pro_amount*pro_price else 0 end'第四季度'  
  6. from order,item where order.order_id = item.order_id  
  7. group by client_id  

按周统计

  1. select client_id ,  
  2.   sum(case when datepart(week,order_time) = 1 then pro_amount*pro_price else 0 end'第一周',  
  3.   sum(case when datepart(week,order_time) = 2 then pro_amount*pro_price else 0 end'第二周',  
  4.   sum(case when datepart(week,order_time) = 3 then pro_amount*pro_price else 0 end'第三周',  
  5.   sum(case when datepart(week,order_time) = 4 then pro_amount*pro_price else 0 end'第四周',  
  6.   ......................  
  7. from order,item where order.order_id = item.order_id  
  8. group by client_id  

按日统计:

  1. select client_id , convert(varchar(7),order_time,120) 月份,  
  2.   sum(case when datepart(day,order_time) = 1 then pro_amount*pro_price else 0 end'1',  
  3.   sum(case when datepart(day,order_time) = 2 then pro_amount*pro_price else 0 end'2',  
  4.   sum(case when datepart(day,order_time) = 3 then pro_amount*pro_price else 0 end'3',  
  5.   sum(case when datepart(day,order_time) = 4 then pro_amount*pro_price else 0 end'4',  
  6.   ......................  
  7.   sum(case when datepart(day,order_time) = 4 then pro_amount*pro_price else 0 end'31'  
  8. from order,item where order.order_id = item.order_id  
  9. group by client_id,convert(varchar(7),order_time,120)  

按周一、二计算(假设order_time为日期型数据,即不含有时,分,秒等):

 

  1. select client_id ,  
  2.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) - 1 then pro_amount*pro_price else 0 end'周日',  
  3.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) then pro_amount*pro_price else 0 end'周一',  
  4.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) + 1 then pro_amount*pro_price else 0 end'周二',  
  5.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) + 2 then pro_amount*pro_price else 0 end'周三',  
  6.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) + 3 then pro_amount*pro_price else 0 end'周四',  
  7.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) + 4 then pro_amount*pro_price else 0 end'周五',  
  8.   sum(case when order_time = DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) + 5 then pro_amount*pro_price else 0 end'周六'  
  9. from order,item where order.order_id = item.order_id  
  10. group by client_id  

按周一、二计算(假设order_time为日期型数据,同时含有时,分,秒等,要转换一下。):

  1. select client_id ,  
  2.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)-1,120) then pro_amount*pro_price else 0 end'周日',  
  3.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0),120) then pro_amount*pro_price else 0 end'周一',  
  4.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+1,120) then pro_amount*pro_price else 0 end'周二',  
  5.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+2,120) then pro_amount*pro_price else 0 end'周三',  
  6.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+3,120) then pro_amount*pro_price else 0 end'周四',  
  7.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+4,120) then pro_amount*pro_price else 0 end'周五',  
  8.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+5,120) then pro_amount*pro_price else 0 end'周六'  
  9. from order,item where order.order_id = item.order_id  
  10. group by client_id  
  11. --按周一、二计算(假设order_time为日期型数据,同时含有时,分,秒等,要转换一下。)  
  12. select client_id ,  
  13.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)-1,120) then pro_amount*pro_price else 0 end'周日',  
  14.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0),120) then pro_amount*pro_price else 0 end'周一',  
  15.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+1,120) then pro_amount*pro_price else 0 end'周二',  
  16.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+2,120) then pro_amount*pro_price else 0 end'周三',  
  17.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+3,120) then pro_amount*pro_price else 0 end'周四',  
  18.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+4,120) then pro_amount*pro_price else 0 end'周五',  
  19.   sum(case when convert(varchar(10),order_time,120) = convert(varchar(10),DATEADD(wk,DATEDIFF(wk,0,getdate()),0)+5,120) then pro_amount*pro_price else 0 end'周六'  
  20. from order,item where order.order_id = item.order_id  
  21. group by client_id  

[周期]根据当前时间计算每周星期几、每月几号、每年几月几日的执行时间

1、每周。例如计算每周一的时间。 2、每月。例如计算每月15号的时间。 3、每年。例如计算每年5月20号的时间。 String RWFKSJ = ""; //任务反馈时间 Strin...
  • YapingGe2014
  • YapingGe2014
  • 2015年05月26日 14:45
  • 1129

oracle统计一年中每个月的数据

使用场景:统计支出表中某人某年的每个月的支出情况 1.创建表如下(oracle) --支出表   create table PAY   (     ID ...
  • 99guo
  • 99guo
  • 2014年02月19日 10:59
  • 8281

每天,每周,每月,每年程序员该做的事情

程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   ...
  • cuifugang124428
  • cuifugang124428
  • 2014年05月25日 20:47
  • 331

程序员每天、每周、每月、每年该做的事

一、程序员每天该做的事 1、总结自己一天任务的完成情况  最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   把明天要做...
  • honghailiang888
  • honghailiang888
  • 2016年06月21日 11:47
  • 393

每天,每周,每月,每年程序员该做的事情

程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   ...
  • u010985819
  • u010985819
  • 2014年05月26日 08:13
  • 127

每天,每周,每月,每年程序员该做的事情

http://www.itjhwd.com/cxysq/# 程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记...
  • wangyi1988wang
  • wangyi1988wang
  • 2014年05月25日 23:48
  • 575

每天,每周,每月,每年程序员该做的事情

程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   ...
  • nanmuling
  • nanmuling
  • 2014年06月12日 11:48
  • 285

每天,每周,每月,每年程序员该做的事情

程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   ...
  • yyylll62803112
  • yyylll62803112
  • 2014年05月25日 20:20
  • 410

每天,每周,每月,每年程序员该做的事情

程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   ...
  • mitesi
  • mitesi
  • 2014年05月25日 21:16
  • 480

每天,每周,每月,每年程序员该做的事情

程序员每天该做的事 1、总结自己一天任务的完成情况   最好的方式是写工作日志,把自己今天完成了什么事情,遇见了什么问题都记录下来,日后翻看好处多多 2、考虑自己明天应该做的主要工作   ...
  • qazwsx185313301
  • qazwsx185313301
  • 2014年05月25日 22:36
  • 563
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL2000 统计每周,每月,每季,每年的数据
举报原因:
原因补充:

(最多只允许输入30个字)