如何统计每周,每月,每季,每年的数据?谢谢大家

[newppstream]

表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

[dawugui]

--年
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

[dawugui]

--季度
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

[welove1983]

create table [order]
(
order_id int,
client_id int,
order_time datetime
)
create table item
(
item_id int,
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 month(order_time) = 1 then pro_amount*pro_price else 0 end) '1月',
sum(case when month(order_time) = 2 then pro_amount*pro_price else 0 end) '2月',
sum(case when month(order_time) = 3 then pro_amount*pro_price else 0 end) '3月',
sum(case when month(order_time) = 4 then pro_amount*pro_price else 0 end) '4月',
sum(case when month(order_time) = 5 then pro_amount*pro_price else 0 end) '5月',
sum(case when month(order_time) = 6 then pro_amount*pro_price else 0 end) '6月',
sum(case when month(order_time) = 7 then pro_amount*pro_price else 0 end) '7月',
sum(case when month(order_time) = 8 then pro_amount*pro_price else 0 end) '8月',
sum(case when month(order_time) = 9 then pro_amount*pro_price else 0 end) '9月',
sum(case when month(order_time) = 10 then pro_amount*pro_price else 0 end) '10月',
sum(case when month(order_time) = 11 then pro_amount*pro_price else 0 end) '11月',
sum(case when 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

[welove1983]

1 235 0 0 0 0 80 0 0 0 0 0 0
3 0 90 0 0 0 0 0 0 0 0 0 0

[dawugui]

--周
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

[newppstream]

谢谢大家,我先试试

[dawugui]

--日
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)

[newppstream]

谢谢:dawugui(潇洒老乌龟),按周计算是统计从周一到周日的数据.

[dawugui]

newppstream() ( ) 信誉：100
谢谢:dawugui(潇洒老乌龟),按周计算是统计从周一到周日的数据.

2.按周得到总金额:
client_id 周一 周二 ....周日
1
3

那得把月份带进去才行了.不好意思.

[dawugui]

--按周
select client_id , convert(varchar(7),order_time,120) 月份,
sum(case when datepart(weekday,order_time) = 1 then pro_amount*pro_price else 0 end) '周日',
sum(case when datepart(weekday,order_time) = 2 then pro_amount*pro_price else 0 end) '周一',
sum(case when datepart(weekday,order_time) = 3 then pro_amount*pro_price else 0 end) '周二',
sum(case when datepart(weekday,order_time) = 4 then pro_amount*pro_price else 0 end) '周三',
sum(case when datepart(weekday,order_time) = 5 then pro_amount*pro_price else 0 end) '周四',
sum(case when datepart(weekday,order_time) = 6 then pro_amount*pro_price else 0 end) '周五',
sum(case when datepart(weekday,order_time) = 7 then pro_amount*pro_price else 0 end) '周六'
from order,item where order.order_id = item.order_id
group by client_id,convert(varchar(7),order_time,120)

[dawugui]

如果你的时间跨年的话,没个查询把年份带进去.

select ....year(order_time)

group by ... year(order_time)

[newppstream]

谢谢dawugui(潇洒老乌龟),我想得到本周的从周一到周日的,如果按月,就不完整.

[dawugui]

--本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

--本周的星期日
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)-1

[newppstream]

谢谢dawugui(潇洒老乌龟):
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
和SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)-1放在哪儿.

[dawugui]

--按周一、二计算(假设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

[dawugui]

--按周一、二计算(假设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

[qian21h]

MARK
正需要呢
谢谢

[76ersjy2]

学习

[yjddit]

卖ASP.NET分页过程和操作类100RMB
山穷水尽，卖东西了，卖完了回家种田
谁都不要说谁，有你也可以来这叫唤
可在线演示ＱＱ：110343042

[cdygf]

mark