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

来源：互联网发布：linux rpm 路径编辑：程序博客网时间：2024/06/05 05:55

表order(订单表)

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

表item(明细表)

item_id order_id(明细表) pro_id(产品ID) pro_amount(数量) pro_price(单价)
1 1 1 10 10.00
2 1 3 5 15.00
3 2 1 5 12.00
4 3 2 10 8.00
5 4 3 2 15.00
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
================================================================

数据准备:

create table [order]
(
order_id int,
client_id int,
order_time datetime
)
create table item
(
item_idint,
order_id int,
pro_id int,
pro_amount int,
pro_price int
)
insert into [order]
select 1,1,'2007-1-5'
union all
select 2,1,'2007-1-7'
union all
select 3,1,'2007-6-5'
union all
select 4,3,'2007-2-5'
union all
select 5,3,'2007-2-18'
insert into item
select 1,1,1,10,10
union all
select 2,1,3,5,15
union all
select 3,2,1,5,12
union all
select 4,3,2,10,8
union all
select 5,4,3,2,15
union all
select 6,5,2,6,10

按年统计:

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

按季度统计:

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

按周统计

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

按日统计:

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

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

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

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

select client_id ,
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) '周日',
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) '周一',
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) '周二',
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) '周三',
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) '周四',
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) '周五',
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) '周六'
from order,item where order.order_id = item.order_id
group by client_id
--按周一、二计算(假设order_time为日期型数据，同时含有时，分，秒等，要转换一下。)
select client_id ,
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) '周日',
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) '周一',
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) '周二',
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) '周三',
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) '周四',
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) '周五',
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) '周六'
from order,item where order.order_id = item.order_id
group by client_id