狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

數據表Order.dbo結構：
OrderNo varchar(10), ---單號
GetDate Varchar(10), ---日期
Sales Varchar(10), ---員
ItemNo Varchar(20), ---產品號
Amount Smallint ---金額

數據記錄如下：
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，得到按月份統計的如下結果：
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 '業務員',
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 '業務員',
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

業務員 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

（所影響的行數為 2 行）