91网首页-91网页版-91网在线观看-91网站免费观看-91网站永久视频-91网站在线播放

數(shù)據(jù)表Order.dbo結(jié)構(gòu)：
OrderNo varchar(10), ---單號
GetDate Varchar(10), ---日期
Sales Varchar(10), ---員
ItemNo Varchar(20), ---產(chǎn)品號
Amount Smallint ---金額

數(shù)據(jù)記錄如下：
001 2004-01-01 ABC A001 1000
001 2004-01-15 QQQ A001 5000
002 2004-02-01 ABC A001 2000
003 2004-03-01 ABC A001 3000
004 2004-06-01 ABC A001 6000
......

要求輸入欲查詢的年度條件如：2004，得到按月份統(tǒng)計的如下結(jié)果：
Slaes 200401 200402 200403 200404 200405 200406 200407 200408 200409 200410 200411
----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
ABC 1000 2000 3000 6000
QQQ 5000

第一種答案：

declare @sql varchar(8000)
set @sql = 'select Sales'
select @sql = @sql + ',sum(case left(GetDate,7) when '''+left(GetDate,7)+''' then Amount end) ['+ replace(left(GetDate,7),'-','')+']'

from (select distinct left(GetDate,7) as GetDate from test) as a
select @sql = @sql+' from test group by Sales'
print @sql
exec(@sql)

第二種答案：

SQL語句:
select
sales as '業(yè)務(wù)員',
sum(case when month(getdatetime)=1 then amount end) as '1月份總額',
sum(case when month(getdatetime)=2 then amount end) as '2月份總額',
sum(case when month(getdatetime)=3 then amount end) as '3月份總額',
sum(case when month(getdatetime)=4 then amount end) as '4月份總額',
sum(case when month(getdatetime)=5 then amount end) as '5月份總額',
sum(case when month(getdatetime)=6 then amount end) as '6月份總額',
sum(case when month(getdatetime)=7 then amount end) as '7月份總額',
sum(case when month(getdatetime)=8 then amount end) as '8月份總額',
sum(case when month(getdatetime)=9 then amount end) as '9月份總額',
sum(case when month(getdatetime)=10 then amount end) as '10月份總額',
sum(case when month(getdatetime)=11 then amount end) as '11月份總額',
sum(case when month(getdatetime)=12 then amount end) as '12月份總額'
from you_table group by sales
--********************************************************************************
--存儲過程
create procedure sp_ordertable
@Year int
as
select
sales as '業(yè)務(wù)員',
sum(case when month(getdatetime)=1 then amount end) as '1月份總額',
sum(case when month(getdatetime)=2 then amount end) as '2月份總額',
sum(case when month(getdatetime)=3 then amount end) as '3月份總額',
sum(case when month(getdatetime)=4 then amount end) as '4月份總額',
sum(case when month(getdatetime)=5 then amount end) as '5月份總額',
sum(case when month(getdatetime)=6 then amount end) as '6月份總額',
sum(case when month(getdatetime)=7 then amount end) as '7月份總額',
sum(case when month(getdatetime)=8 then amount end) as '8月份總額',
sum(case when month(getdatetime)=9 then amount end) as '9月份總額',
sum(case when month(getdatetime)=10 then amount end) as '10月份總額',
sum(case when month(getdatetime)=11 then amount end) as '11月份總額',
sum(case when month(getdatetime)=12 then amount end) as '12月份總額'
from lpy_ordertable where year(getdatetime)=@year group by sales

exec sp_ordertable 2004

業(yè)務(wù)員 1月份總額 2月份總額 3月份總額 4月份總額 5月份總額 6月份總額 7月份總額 8月份總額 9月份總額 10月份總額 11月份總額 12月份總額
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
abc 1000 2000 3000 NULL NULL 6000 NULL NULL NULL NULL NULL NULL
qqq 5000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

（所影響的行數(shù)為 2 行）