利用存储过程实现交叉表格式数据查询的一种通用方法
来源:互联网 发布:js访问style标签样式 编辑:程序博客网 时间:2024/05/22 08:25
利用存储过程实现交叉表格式数据查询的一种通用方法
问题描述:如何生成以下表结构的交叉报表格式的数据查询。
CREATE TABLE [dbo].[testTable] (
[id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dDate] [datetime] NOT NULL ,
[dTime] [int] NOT NULL ,
[value] [decimal](18, 4) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[testTable] ADD
CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED
(
[id],
[dDate],
[dTime]
) ON [PRIMARY]
GO
数据如下表所示:
Id
dDate
dTime
value
压力1
2005-1-1
8
15.23
压力1
2005-1-1
9
14.26
压力1
2005-1-1
10
18.78
压力1
2005-1-1
11
20.12
压力1
2005-1-1
12
30.25
压力2
2005-1-1
8
18.23
压力2
2005-1-1
9
19.26
压力2
2005-1-1
10
14.78
压力2
2005-1-1
11
22.12
压力2
2005-1-1
12
20.25
压力3
2005-1-1
8
16.23
压力3
2005-1-1
9
29.26
压力3
2005-1-1
10
24.78
压力3
2005-1-1
11
25.12
压力3
2005-1-1
12
24.25
表一
生成的报表格式如下:
日期
时间
压力1
压力2
压力3
2005-1-1
8
15.23
18.23
16.23
2005-1-1
9
14.26
19.26
29.26
2005-1-1
10
18.78
14.78
24.78
2005-1-1
11
20.12
22.12
25.12
2005-1-1
12
30.25
20.25
24.25
表二
表格经过转换(转换为交叉表格式),压缩了重复的数据,而且也便于查看和比较数据间的关系。
实现原理:
1.生成时间临时表,格式如下表:
日期
时间
2005-1-1
8
2005-1-1
9
2005-1-1
10
2005-1-1
11
2005-1-1
12
表三
2.再生成以下临时表,格式如下表:
日期
时间
压力1
2005-1-1
8
15.23
2005-1-1
9
14.26
2005-1-1
10
18.78
2005-1-1
11
20.12
2005-1-1
12
30.25
表四
日期
时间
压力2
2005-1-1
8
18.23
2005-1-1
9
19.26
2005-1-1
10
14.78
2005-1-1
11
22.12
2005-1-1
12
20.25
表五
日期
时间
压力3
2005-1-1
8
16.23
2005-1-1
9
29.26
2005-1-1
10
24.78
2005-1-1
11
25.12
2005-1-1
12
24.25
表六
3.然后使用SQL语句把它们连接起来,形成表二格式。
4.为了使得存储过程具有极大的灵活性,能够适应不同的表名称、不同的列名称、查询不同的列和查询不同的时间,应当使用动态SQL语句编写存储过程。
存储过程语句如下:
@tableName varchar(100),@columnFieldName varchar(100),@dateFieldName varchar(100),@timeFieldName varchar(100),@valueFieldName varchar(100),
@searchColumn varchar(1000),@SearchTime varchar(1000)
-- 为查询表名,@columnFieldName 为查询列字段名,@dateFieldName 为查询日期字段名,@timeFieldName 为查询时间字段名,@valueFieldName 为查询值字段名
@tableName-- 为要查询的列的字符串,@SearchTime 为要查询的时间字符串
AS
declare @i @searchColumnint,@j int,@dTime int,@timeRowNum int,@colID int
declare @dDate datetime
declare @sql varchar(4000),@sqlTableName varchar(1000),@sqlJoinSelect varchar(4000),@sqlJoinFrom varchar(4000)
set @i = dbo.fGetParamTotal(@searchColumn)
set @timeRowNum = dbo.fGetParamTotal(@SearchTime)
set @sql = ''
set @sqlJoinSelect = 'select Convert(varchar(2),#tempTime.dTime) +'':00''as 时间'
set @sqlJoinFrom = ' from #tempTime '
set @colID = 1
-- 建立临时时间表,并插入数据
CREATE TABLE [dbo].[#tempTime] ([dDate] [datetime] NOT NULL ,[dTime] [int] NOT NULL ) ON [PRIMARY]
while (@timeRowNum >0)
begin
set @dDate = Convert(datetime,dbo.fGetParamValue(@SearchTime,@timeRowNum))
set @dDate = Convert(datetime,dbo.fToDate(@dDate))
set @dTime = datepart(hour,Convert(datetime,dbo.fGetParamValue(@SearchTime,@timeRowNum)))
INSERT INTO #tempTime(dDate, dTime) VALUES (@dDate, @dTime)
set @timeRowNum = @timeRowNum - 1
end
--建立动态sql语句生成临时表,并且连接临时表
if @i <> 0
begin
while (@i > 0)
begin
set @sqlTableName = '#tempValue'+ Convert(varchar(2),@colID)
set @sql = @sql + N'select '+ @columnFieldName + N','+@dateFieldName +N','+@timeFieldName+N','+@valueFieldName +N' into ' + @sqlTableName + N'
from '+@tableName+N' where '+ @columnFieldName + N' = '''+ Convert(varchar(1000),dbo.fGetParamValue(@searchColumn,@colID)) +N''''
--求第一个查询日期和时间
set @dDate = Convert(datetime,dbo.fGetParamValue(@SearchTime,1))
set @dDate = Convert(datetime,dbo.fToDate(@dDate))
set @dTime = datepart(hour,Convert(datetime,dbo.fGetParamValue(@SearchTime,1)))
set @sql = @sql + N' and (((' + @dateFieldName + N' = ''' + Convert(varchar(100),@dDate,121) + N''') and ('+ @timeFieldName + N' = '+ Convert(varchar(100),@dTime) + N'))'
set @j = 2
while (@j <= dbo.fGetParamTotal(@SearchTime))
begin
set @dDate = Convert(datetime,dbo.fGetParamValue(@SearchTime,@j))
set @dDate = Convert(datetime,dbo.fToDate(@dDate))
set @dTime = datepart(hour,Convert(datetime,dbo.fGetParamValue(@SearchTime,@j)))
set @sql = @sql + N' or ((' + @dateFieldName + N' = ''' + Convert(varchar(100),@dDate,121) + N''') and ('+ @timeFieldName + N' = '+ Convert(varchar(100),@dTime) + N'))'
set @j = @j +1
end
set @sql = @sql + N')'
set @sqlJoinSelect = @sqlJoinSelect + N','+@sqlTableName +N'.'+@valueFieldName+N' as ' + Convert(varchar(1000),dbo.fGetParamValue(@searchColumn,@colID))
set @sqlJoinFrom = @sqlJoinFrom + N' left join '+ @sqlTableName + N' on #tempTime.dDate = '+ @sqlTableName + N'.'+@dateFieldName + N' and #tempTime.dTime = '+ @sqlTableName + N'.'+@timeFieldName
set @i = @i - 1
set @colID = @colID +1
end
exec(@sql + @sqlJoinSelect + @sqlJoinFrom +' order by #tempTime.dDate,#tempTime.dTime')
--print @sql + @sqlJoinSelect + @sqlJoinFrom +' order by #tempTime.dDate,#tempTime.dTime'
end
GO
其中使用三个函数,代码分别如下:
函数一:
(@param varchar(1000))
RETURNS int
AS
BEGIN
declare @ParamTotal int
set @ParamTotal = 0
if @param = ''
return 0
while (charindex(',',@param) <> 0)
begin
set @ParamTotal = @ParamTotal + 1
set @param = substring(@param,charindex(',',@param)+1,len(@param))
end
return @ParamTotal + 1
END
函数二:
(@param varchar(1000),@id int)
RETURNS varchar(1000)
AS
BEGIN
declare @ParamTotal int
declare @i int
declare @subString varchar(1000)
set @ParamTotal = dbo.fGetParamTotal(@param)
set @subString = ''
set @i = 1
if @id <= 0
return @subString
if @ParamTotal >= @id
begin
while (@id > @i)
begin
-- set @ParamTotal = @ParamTotal + 1
set @param = substring(@param,charindex(',',@param)+1,len(@param))
set @i = @i + 1
end
if (charindex(',',@param) = 0)
set @subString = @param
else
set @subString = substring(@param,1,charindex(',',@param) - 1)
end
return @subString
END
函数三:
RETURNS varchar(10)
AS
begin
declare @Date varchar(10)
set @Date = convert(char(4),year(@DateTime)) + '-' + convert(varchar(2),month(@DateTime)) + '-' + convert(varchar(2),day(@DateTime))
return @Date
end
存储过程的参数@searchColumn 为要查询的列的字符串,@SearchTime 为要查询的时间字符串,字符串中用逗号分割数据。
执行存储过程的语句如下所示:
pSearchData 'testTable','id','dDate','dTime','value','压力1, 压力2, 压力3','2005-1-1 8:00:00,2005-1-1 9:00:00,2005-1-1 12:00:00'
- 利用存储过程实现交叉表格式数据查询的一种通用方法
- MSSQL2000 通用交叉表查询存储过程
- 实现通用交叉表统计存储过程
- 实现分页的通用存储过程(第一种)
- JSP中表单数据存储的一种通用方法
- 对于JSP中表单数据存储的一种通用方法
- JSP中表单数据存储的一种通用方法
- JSP中表单数据存储的一种通用方法
- 一个通用的交叉制表存储过程
- 一个通用的交叉制表存储过程
- 利用Oracle存储过程实现中国式的交叉统计报表
- 利用元数据编写查询的通用方法
- .NET-一种实现表格数据滚动显示的方法
- 交叉表 通用存储过程
- 利用反射实现JDBC的数据库通用查询方法
- SQL Server通用分页存储过程:利用SQL Server未公开的存储过程实现
- SQL Server通用分页存储过程:利用SQL Server未公开的存储过程实现
- SQL Server通用分页存储过程:利用SQL Server未公开的存储过程实现
- 订单确认bapi co15
- Using PERL,VB.NET,JAVASCRIPT to Resize pictures
- sscanf() - 从一个字符串中读进与指定格式相符的数据.
- DB2 账户及用户标识
- EXCEL中从单元格中取日期截止到月份的公式及示例
- 利用存储过程实现交叉表格式数据查询的一种通用方法
- struts使用总结一:慎用actionServlet成员变量,actionServlet生命周期,actionForm生命周期,struts上传注意事项
- ASP.Net中自定义Http处理及应用之HttpModule篇
- datatable常用方法
- wince5编译SDK出错的解决办法
- Vector容器简介
- 网站备案风波迭起
- 解读javax.microedition.lcdui.Font 转
- 公告~~~