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  

一个sql题目, 统计每年每月的信息

1 题目描述 2 建表和插入数据 CREATE TABLE sales( id INT AUTO_INCREMENT PRIMARY KEY, YEAR VARCHAR(10)...
  • jianfpeng241241
  • jianfpeng241241
  • 2015-11-16 12:43:41
  • 1362

查询每个年龄的顾客消费金额数的SQL语句

有三个表,书籍表book(id,name,price)和顾客表customer(id,name,age)以及订单表orders(id,bookid,customerid)。 请写出查询每个年龄的顾客消...
  • xukun5137
  • xukun5137
  • 2016-06-22 21:34:55
  • 2479

sql查询单个项目的全年总和,按月份统计怎么实现?

SELECT     a.id, a.clientName,      SUM(CASE WHEN DATEPART(year, b.GoDate)='2014' AND DATEPART(MON...
  • zz770750140
  • zz770750140
  • 2017-06-28 23:37:12
  • 1119

每周星期几,每月几号的日期处理

根据某个日期和所选择的是周几(或者几号),返回大于这个日期,最近的一个时间。 比如: 1.某周的某一星期 currentDate:2016-4-22 10:00 (星期五) tweekDay:...
  • u010828718
  • u010828718
  • 2016-04-22 11:34:19
  • 518

SQL语句统计每小时,每天、每月、每年的_数据

--by hours SELECT TO_CHAR(oper_time, 'hh24') AS "Each HOUR", COUNT(id) AS qty FROM T_SYS_OPER_LOG ...
  • xiuhaijuanqiang
  • xiuhaijuanqiang
  • 2016-06-13 16:21:52
  • 4827

常用的数据统计Sql 总结

1. 统计各个条件下的数据 select BatchId,sum(CardSum) 总金额, sum(case when Status=1 then CardSum else 0 end) as 已...
  • yin554393109
  • yin554393109
  • 2016-08-03 09:56:42
  • 5608

mysql查询每天每周每月每年的数据方法

mysql日期函数与自定义变量使用或查询方法
  • h363659487
  • h363659487
  • 2017-07-05 15:56:41
  • 4750

分享[ASP.NET]SQL语句统计每天、每月、每年的数据 作者:吴立星

1、每年 select year(ordertime) 年, sum(Total) 销售合计 from 订单表 group by year(ordertime) 2、每月 sele...
  • WULIXING
  • WULIXING
  • 2016-11-17 17:31:16
  • 667

sql统计每5分钟的次数

一个按五分钟分段统计的例   create table tb(时间 datetime , 金额 int) insert into tb values('2007-1-1 10:00:...
  • t_kong
  • t_kong
  • 2013-12-13 15:17:34
  • 941

SQL语句统计每天、每月、每年的数据

SQL语句统计每天、每月、每年的数据 1、每年 select year(ordertime) 年, sum(Total) 销售合计 from 订单表 grou...
  • ghostyusheng
  • ghostyusheng
  • 2016-11-22 11:16:01
  • 345
收藏助手
不良信息举报
您举报文章:SQL2000 统计每周,每月,每季,每年的数据
举报原因:
原因补充:

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