生活随笔
收集整理的這篇文章主要介紹了
SQL2000 统计每周,每月,每季,每年的数据
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
表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?? ?? 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??
總結
以上是生活随笔為你收集整理的SQL2000 统计每周,每月,每季,每年的数据的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
