最近碰到一需求,是酱紫的: 一个销售商品表, 每年每月每天都卖出N种不同种类的商品, 最后要统计每月每种商品的销售额. 或每年的, 在此我模拟创建了一个简单的表, 主要说明经验和大家一块分享, 若发现有问题, 欢迎随时指教.
如下:(此表过于简单, 主要用于说明交叉表)
按某一年的多月查询结果如下:
多年对比查询结果如下:
具体实现代码如下:
----------创建一个交叉表--------------
create table salesRecord (
id int primary key identity(1,1) not null,
[date] datetime not null,
[goods] varchar(10) not null,
[money] decimal(10,2) default 0
)
----------向交叉表中插入记录--------------
insert into salesRecord values('2009-3-2','显示器',2300)
insert into salesRecord values('2009-1-3','键盘',30)
insert into salesRecord values('2009-3-2','机箱',200)
insert into salesRecord values('2009-7-2','鼠标',55)
insert into salesRecord values('2009-12-2','硬盘',455)
insert into salesRecord values('2009-9-2','屏保膜',10)
insert into salesRecord values('2009-4-28','硬盘',455)
insert into salesRecord values('2009-2-9','电源线',15)
insert into salesRecord values('2009-3-5','主板',605)
insert into salesRecord values('2009-8-15','摄像头',120)
insert into salesRecord values('2009-5-18','U盘',105)
insert into salesRecord values('2008-5-28','U盘',105)
insert into salesRecord values('2009-10-11','U盘',80)
-------创建访问交叉表的查询语句生成函数---(商品种类赿多,查出出的表字段就赿多)--------------
/*
* @year 某一年
* @ym 有可能是年 有可能是月, 看后边的@flag决定
* @month 某一月
* @flag 对前边参数的说明标记.
* 如果 YY 则某年到某年查询,前两个参数为开始年,结束年,第三个参数无效(但仍然需要传入任意整型值)
* 如果YMM 某一年从几月到几月查询 ,第一个参数为某年, 后两个参数为开始月和结束月.
* @returns 返回组装后的字符串.
*/
create function getQueryStr (@y int,@ym int,@m int,@flag varchar(3))
returns varchar(7000)
as
begin
declare @tempStr varchar(20)
declare @rtnStr varchar(7000)
declare @condStr varchar(500)
--判断如果是YY则是查某一年到某一年,组装查询串及条件
if(@flag='YY')
begin
set @rtnStr=' select year(date) as ''年份'', '
set @condStr=' year(date)>= '+cast(@y as varchar) +' and year(date)<= '+cast(@ym as varchar)+' group by year(date)'
end
else if(@flag='YYM')
--否则查某一年的某一月到某一月,组装查询串及条件
begin
set @rtnStr=' select cast(month(date) as varchar)+''月'' as ''月份'', '
set @condStr=' year(date)= '+cast(@y as varchar)+' and month(date)>= '+cast(@ym as varchar)+' and month(date)<= '+cast(@m as varchar)+' group by month(date)'
end
declare getOne cursor
for
select distinct goods from salesRecord
open getOne
fetch next from getOne into @tempStr
while @@fetch_status=0
begin
set @rtnStr = @rtnStr+' sum(case goods when '''+@tempStr+''' then money else 0 end) as '''+@tempStr+''','
fetch next from getOne into @tempStr
end
close getOne
deallocate getOne
set @rtnStr=left(@rtnStr,len(@rtnStr)-1)
set @rtnStr=@rtnStr+' from salesRecord where '+@condStr
return @rtnStr
end
----------按多年查询------------
declare @str varchar(7000)
set @str=dbo.getQueryStr(2008,2009,0,'YY');
exec(@str)
----------按一年多月查询------------
declare @str varchar(7000)
set @str=dbo.getQueryStr(2009,0,12,'YYM');
exec(@str)
----------查询下源表中的数据对比下------------
select * from salesRecord order by date
---------如果不用了可以删除函数和表--------------
drop function getQueryStr
drop table salesRecord