利用SQL生成月报表统计数据
2012-06-12 09:29 清新风 阅读(6990) 评论(0) 编辑 收藏 举报- 摘要:系统运行了一段时间,使用者往往都想知道系统的运行状态,知道自己公司的业绩等,这就经常要做些适用满足客户需求的报表.
系统运行了一段时间,使用者往往都想知道系统的运行状态,知道自己公司的业绩等..;这就经常要做些适用满足客户需求的报表.其中下面这个案例是开发人员经常要面对的,我以前也碰到过,只是有时候没有用sql去解决它,以前是在程序里头作些处理来达到类似的功能.说了这么还没有阐明到底是什么需求.
我这里有一个交易表 db_Transcation 记录了每个客户的交易情况.现在我想统计一个月内每天的交易情况.下面我用表格说明具体需求.
db_Transaction的结构简化如下:
create table db_Transaction
(
dt_id int identify(1) primary key,
dt_clientNo varchar(50) not null ,
dt_money int not null,
dt_Datetime datetime
)
具有数据如下:
dt_id dt_clientNo dt_money dt_DateTime
1 001 10 2007-11-1
2 001 11 2007-11-2
3 001 40 2007-11-3
..................
我需要得出如下统计格式
dt_clientNo 1日 2日 3日 ........31日
001 10 11 40
........
以前一般是先group by dt_clientNo 这个字段,然后在vs.net里头,构造一个结构类似需求的DataSet;然后填充数据
其实我们完全能够用SQL完成这个需求;
设计的SQL脚本如下:
--建立存储过程(参数:年、月)
CREATE PROC Report_Details
@YEAR INT,
@MONTH INT
AS
DECLARE @SQL VARCHAR(8000)
DECLARE @DayOfMonth INT
DECLARE @I INT
SET @DayOfMonth=DATEDIFF(day,cast(@YEAR as varchar)+ '- '+cast(@MONTH as varchar)+ '-01 ',
cast(@YEAR as varchar)+ '- '+cast(@MONTH+1 as varchar)+ '-01 ') -- 获取这个月的天数
SET @SQL= 'SELECT dt_clientNo'
SET @I=1
WHILE @I <=@DayOfMonth
BEGIN
SET @SQL=@SQL+ ',[ '+CAST(@I AS VARCHAR(10))+ ']=0 '
SET @I=@I+1
END
SET @SQL=@SQL+ ' INTO Tmp3 FROM (SELECT DISTINCT NO FROM TEST) X '
EXEC(@SQL) --生成表结构并初试化为0
我这里有一个交易表 db_Transcation 记录了每个客户的交易情况.现在我想统计一个月内每天的交易情况.下面我用表格说明具体需求.
db_Transaction的结构简化如下:
create table db_Transaction
(
dt_id int identify(1) primary key,
dt_clientNo varchar(50) not null ,
dt_money int not null,
dt_Datetime datetime
)
具有数据如下:
dt_id dt_clientNo dt_money dt_DateTime
1 001 10 2007-11-1
2 001 11 2007-11-2
3 001 40 2007-11-3
..................
我需要得出如下统计格式
dt_clientNo 1日 2日 3日 ........31日
001 10 11 40
........
以前一般是先group by dt_clientNo 这个字段,然后在vs.net里头,构造一个结构类似需求的DataSet;然后填充数据
其实我们完全能够用SQL完成这个需求;
设计的SQL脚本如下:
--建立存储过程(参数:年、月)
CREATE PROC Report_Details
@YEAR INT,
@MONTH INT
AS
DECLARE @SQL VARCHAR(8000)
DECLARE @DayOfMonth INT
DECLARE @I INT
SET @DayOfMonth=DATEDIFF(day,cast(@YEAR as varchar)+ '- '+cast(@MONTH as varchar)+ '-01 ',
cast(@YEAR as varchar)+ '- '+cast(@MONTH+1 as varchar)+ '-01 ') -- 获取这个月的天数
SET @SQL= 'SELECT dt_clientNo'
SET @I=1
WHILE @I <=@DayOfMonth
BEGIN
SET @SQL=@SQL+ ',[ '+CAST(@I AS VARCHAR(10))+ ']=0 '
SET @I=@I+1
END
SET @SQL=@SQL+ ' INTO Tmp3 FROM (SELECT DISTINCT NO FROM TEST) X '
EXEC(@SQL) --生成表结构并初试化为0
SET @SQL= ' '
SET
@I=1
WHILE @I <=@DayOfMonth
BEGIN
SET @SQL=@SQL+ 'UPDATE Tmp3 SET
[ '+CAST(@I AS VARCHAR(10))+ ']=a.[Money]
FROM db_Transaction
a,Tmp3 b WHERE
a.dt_clientNo=b.dt_clientNo AND DAY(a.[dt_DateTime])=
'+CAST(@I AS VARCHAR(10))+CHAR(10)
SET @I=@I+1
END --赋值
EXEC(@SQL)
SELECT * FROM Tmp3
GO
--调用过程
EXEC
Report_Details '2006 ', '1 '
--删除测试环境
DROP
TABLE Tmp3
DROP PROC Report_Details