- 浏览: 21369536 次
- 性别:
- 来自: 杭州
最新评论
-
ZY199266:
配置文件还需要额外的配置ma
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
ZY199266:
我的一访问为什么是 /mavenwebdemo/WEB-I ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
lvgaga:
我又一个问题就是 如果像你的这种形式写。配置文件还需要额外的 ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
lvgaga:
我的一访问为什么是 /mavenwebdemo/WEB-I ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
y1210251848:
你的那个错误应该是项目所使用的目标框架不支持吧
log4net配置(web中使用log4net,把web.config放在单独的文件中)
常用的几种交互表存储过程的实现方法
第一种:
通用的针对单表用的交叉表存储过程
if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[p_qry]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure [dbo].[p_qry]
GO
/*--生成交叉表的简单通用存储过程
根据指定的表名,纵横字段,统计字段,自动生成交叉表
并可根据需要生成纵横两个方向的合计
注意,横向字段数目如果大于纵向字段数目,将自动交换纵横字段
如果不要此功能,则去掉交换处理部分
--邹建 204.06--*/
/*--调用示例
exec p_qry ''syscolumns'',''id'',''colid'',''colid'',1,1
--*/
create proc p_qry
@TableName sysname, --表名
@纵轴 sysname, --交叉表最左面的列
@横轴 sysname, --交叉表最上面的列
@表体内容 sysname, --交叉表的数数据字段
@是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit --为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)
--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s=''declare @a sysname
if(select case when count(distinct [''+@纵轴+''])<count></count>from [''+@TableName+''])=1
select @a=@纵轴,@纵轴=@横轴,@横轴=@a''
exec sp_executesql @s
,N''@纵轴 sysname out,@横轴 sysname out''
,@纵轴 out,@横轴 out
--生成交叉表处理语句
set @s=''
set @s=''''''''
select @s=@s+'''',[''''+cast([''+@横轴+''] as varchar)+'''']=sum(case [''+@横轴
+''] when ''''''''''''+cast([''+@横轴+''] as varchar)+'''''''''''' then [''+@表体内容+''] else 0 end)''''
from [''+@TableName+'']
group by [''+@横轴+'']''
exec sp_executesql @s
,N''@s varchar(8000) out''
,@sql out
--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
when 1 then '',[合计]=sum([''+@表体内容+''])''
else '''' end
,@sum2=case @是否家纵向合计
when 1 then ''[''+@纵轴+'']=case grouping([''
+@纵轴+'']) when 1 then ''''合计'''' else cast([''
+@纵轴+''] as varchar) end''
else ''[''+@纵轴+'']'' end
,@sum3=case @是否家纵向合计
when 1 then '' with rollup''
else '''' end
--生成交叉表
exec(''select ''+@sum2+@sql+@sum1+''
from [''+@TableName+'']
group by [''+@纵轴+'']''+@sum3)
go
第二种:
一个通用的交叉制表存储过程原作者:John Papa, Matthew Shepker1999
Use pubs
GO
exec prCrosstab 'stor_id','ord_date','qty','sales',1,5
结果:
RowHead | Year_1992 | Year_1993 | Year_1994 |
6380 | 0 | 0 | 8 |
7066 | 0 | 50 | 75 |
7067 | 80 | 0 | 10 |
7131 | 0 | 85 | 45 |
7896 | 0 | 60 | 0 |
8042 | 0 | 55 | 25 |
以下为生成脚本:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prCrosstab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prCrosstab]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
功能:交叉制表
参数 描述
@chrRowHead 表示列,在交叉制表的结果中作为第一出现
@chrColHead 表示列,在交叉制表的结果中该列中的数据被变换为新列名称
@chrValue 表示列,在该列中执行聚合函数
@chrSource 源表或视图
@inyType 1-求和,2-平均值,3-最小值,4-最大值,5-计数
@inyGrouping 1-工作日,2-年内的周数,3-月份,4-季度,5-年份
Author:John Papa Matthew Shepker
整理:水如烟(http://blog.csdn.net/lzmtw)
日期:<chsdate w:st="on" isrocdate="False" islunardate="False" day="1" month="5" year="2005"></chsdate>5-1-2005
*/
CREATE PROCEDURE prCrosstab
@chrRowHead char(30),
@chrColHead char(30),
@chrValue char(30),
@chrSource char(30),
@inyType tinyint=1,
@inyGrouping tinyint=0
AS
/* Variables for the procedure */
DECLARE
@chvRow varchar(255),
@chvCol varchar(255),
@chvVal varchar(255),
@chvType varchar(10),
@chvRowType varchar(10),
@chvColType varchar(255),
@chvTemp varchar(255),
@chvColTemp varchar(255),
@chvRowTemp varchar(255),
@intType int,
@intRowType int,
@intColType int,
@chvExec varchar(255),
@chvGroup varchar(255),
@fltTemp float,
@dtmTemp datetime,
@insR smallint,
@intColumn int,
@intReturn int,
@intTemp int,
@intColNameLen int,
@intMaxRowHead int
SET NOCOUNT ON
/* Check if source exists */
IF NOT EXISTS
(SELECT *
FROM sysobjects
WHERE name = @chrSource
AND type IN ('v','u'))
BEGIN
RAISERROR 51001 'Source does not exists.'
RETURN -1
END
/* Check for column existence */
IF NOT EXISTS
(SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.name = @chrSource
AND sc.name = @chrColHead)
BEGIN
RAISERROR 51002 'Invalid @chrColHead name.'
RETURN -1
END
IF NOT EXISTS
(SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.name = @chrSource
AND sc.name = @chrRowHead)
BEGIN
RAISERROR 51002 'Invalid @chrRowHead name.'
RETURN -1
END
IF NOT EXISTS
(SELECT sc.name
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
WHERE so.name = @chrSource
AND sc.name = @chrValue)
BEGIN
RAISERROR 51002 'Invalid @chrValue name.'
RETURN -1
END
/* Verify type is valid (1(sum),2(avg),etc...) */
IF @inyType OR @inyType > 5
BEGIN
RAISERROR 51000 'Invalid crosstab type.'
RETURN -1
END
/* Create typestr to hold aggregate name */
SELECT @chvType=
CASE @inyType
WHEN 1 THEN 'SUM'
WHEN 2 THEN 'AVG'
WHEN 3 THEN 'MAX'
WHEN 4 THEN 'MIN'
WHEN 5 THEN 'COUNT'
ELSE 'SUM'
END
/* Getstandard data type of @chrValue column */
SELECT @chvTemp = t2.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t1 ON t1.usertype = c.usertype
JOIN systypes t2 ON t1.type = t2.type
WHERE t2.usertype
AND t2.usertype 18
AND t2.usertype 80
AND o.type IN ('u','v')
AND o.name = @chrSource
AND c.name = @chrValue
/* Categorize types for aggregate check */
SELECT @intTemp =
CASE
WHEN @chvTemp IN ('int','smallint','tinyint','float','real',
'decimal','numeric','monery','smallmoney') THEN 1
WHEN @chvTemp IN ('datetime','smalldatetime')THEN 3
WHEN @chvTemp IN ('bit','char','varchar') THEN 5
ELSE 100
END
/* validate existing data type is consistent with selected aggregate */
IF @inyType
BEGIN
RAISERROR 51020 'Crosstab type not valid with @chrValue definition.'
RETURN -1
END
/* Hold thedata type for future use */
SELECT @chvColType = RTRIM(
CASE @inyType
WHEN 5 THEN 'int'
ELSE CASE
WHEN @chvTemp IN ('bit','char','varchar') THEN 'int'
WHEN @chvTemp IN ('decimal','numeric') THEN 'float'
ELSE @chvTemp
END
END)
/* Verify grouping is valid for colhead */
IF @inyGrouping OR @inyGrouping > 5
BEGIN
RAISERROR 51010 'Invalid crosstab grouping.'
RETURN -1
END
/* Getstandard data type of @chrColHead column */
SELECT @chvTemp = t2.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t1 ON t1.usertype = c.usertype
JOIN systypes t2 ON t1.type = t2.type
WHERE t2.usertype
AND t2.usertype 18
AND t2.usertype 80
AND o.type IN ('u','v')
AND o.name = @chrSource
AND c.name = @chrColHead
IF UPPER(@chvTemp) NOT IN ('CHAR','VARCHAR')
SELECT @intColtype = 1
ELSE
SELECT @intColtype = 0
/* Get standard data type of @chvRowHead */
SELECT @chvRowType = t2.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t1 ON t1.usertype = c.usertype
JOIN systypes t2 ON t1.type = t2.type
WHERE t2.usertype
AND t2.usertype 18
AND t2.usertype 80
AND o.type IN ('u','v')
AND o.name = @chrSource
AND c.name = @chrRowHead
IF UPPER(@chvRowType) NOT IN ('CHAR','VARCHAR')
SELECT @intRowtype = 1
ELSE
SELECT @intRowtype = 0
/* Categorize types for grouping check */
SELECT @intTemp =
CASE
WHEN @chvTemp IN ('int','smallint','tinyint','float','real',
'decimal','numeric','monery','smallmoney') THEN 1
WHEN @chvTemp IN ('datetime','smalldatetime')THEN 3
WHEN @chvTemp IN ('bit','char','varchar') THEN 5
ELSE 100
END
/* Validate existing data type is consistant with selected grouping */
IF (@intTemp = 5 AND @inyGrouping > 0) OR (@intTemp = 1 AND @inyGrouping > 0)
OR (@intTemp =3 AND @inyGrouping =0)
BEGIN
RAISERROR 51030 'Crosstab grouping not valid with @chvColHead definition.'
RETURN -1
END
/* Check for permission on source */
IF user_id() 1
BEGIN
IF (SELECT COUNT(DISTINCT c.name)
FROM syscolumns c,sysobjects o,sysprotects p,
sysusers u,master..spt_values v
WHERE c.name IN (@chrColHead,@chrRowHead,@chrValue)
AND c.id = o.id
AND p.id = c.id
AND c.colid = v.number
AND v.type = 'p'
AND o.id = object_id(@chrSource)
AND (u.uid = user_id() OR u.uid IN
(SELECT u1.uid
FROM sysusers u1
WHERE u1.gid = u1.uid
AND u1.gid IN
(SELECT u2.gid
FROM sysusers u2
WHERE u2.uid = user_id()
OR u2.uid = user_id('public'))))
AND p.uid = u.uid
AND p.action = 193
AND p.protecttype = 205
AND columns IS NOT NULL
AND CASE SUBSTRING(p.columns,1,1) & 1
WHEN null THEN 255
WHEN 0 THEN CONVERT(tinyint,SUBSTRING(p.columns,v.low,1))
ELSE (CONVERT(tinyint,ISNULL(SUBSTRING(p.columns,v.low,1),0)))
END & v.high 0
AND NOT EXISTS
(SELECT *
FROM syscolumns c5,sysobjects o5,
sysprotects p5,sysusers u5,
master..spt_values v5
WHERE c.name IN (@chrColHead,@chrRowHead,@chrValue)
AND c5.colid = c.colid
AND c5.id = c5.id
AND c5.colid = v5.number
AND v5.type = 'p'
AND o5.id = object_id(@chrSource)
AND (u5.uid = user_id() OR u5.uid IN
(SELECT u6.uid
FROM sysusers u6
WHERE u6.gid = u6.uid
AND u6.gid IN
(SELECT u7.gid
FROM sysusers u7
WHERE u7.uid = user_id()
OR u7.uid = user_id('public'))))
AND p5.uid = u5.uid
AND p5.action = 193
AND p5.protecttype = 206
AND p5.columns IS NOT NULL
AND CASE SUBSTRING(p5.columns,1,1) & 1
WHEN NULL THEN 255
WHEN 0 THEN CONVERT(tinyint,SUBSTRING(p5.columns,v5.low,1))
ELSE (CONVERT(tinyint,ISNULL(SUBSTRING(p5.columns,v5.low,1),0)))
END & v5.high 0)) 3
BEGIN
RAISERROR 51003 'Permission denied on column.'
RETURN -1
END
END
/* Create table #colnames and #rownames */
CREATE TABLE #colnames (colname varchar(255) NULL,colnumber int NULL)
CREATE TABLE #rownames (rowname varchar(255) NULL)
/* Insert distinct column data into #colnames */
SELECT @chvExec = 'insert #colnames select col1,col2 from '
+ '(select distinct col1 = ' +
CASE @intTemp
WHEN 3 THEN
CASE
WHEN @inyGrouping IN (1,3) THEN 'datename(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 3 THEN 'month'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE CASE @inyGrouping
WHEN 2 THEN '''Week'
WHEN 4 THEN '''Quarter'
WHEN 5 THEN '''Year'
END + '_'' + ' + 'datename(' +
CASE @inyGrouping
WHEN 2 THEN 'week'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead)+ ')'
END
ELSE CASE @intColType
WHEN 1 THEN 'convert(varchar(255), ' + RTRIM(@chrColHead) + ')'
ELSE RTRIM(@chrColHead)
END
END + ',col2 = '+
CASE @intTemp
WHEN 3 THEN 'datepart(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 2 THEN 'week'
WHEN 3 THEN 'month'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE '0'
END + ',col3 = '+
CASE @intTemp
WHEN 3 THEN 'datepart(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 3 THEN 'month'
WHEN 2 THEN 'week'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE RTRIM(@chrColHead)
END + ' from ' +RTRIM(@chrSource) + ') xyz order by col3'
--PRINT @chvExec
EXEC(@chvExec)
/* Check column count */
IF (SELECT COUNT(*) FROM #colnames) > 1023
BEGIN
DROP TABLE #colnames
RAISERROR 51004 'Distinct column count exceeded max of 1023.'
RETURN -1
END
/* Verify colnames do not exceed max length */
IF (SELECT MAX(DATALENGTH(RTRIM(colname)) - 1) FROM #colnames) > 29
BEGIN
DROP TABLE #colnames
RAISERROR 51050 'Column data length exceeded max of 30.'
RETURN -1
END
/* If all is OK,continue to add #rownames data */
SELECT @chvExec = 'insert #rownames select distinct ' +
CASE @intRowType
WHEN 1 THEN 'convert(varchar(255), '
ELSE ''
END + RTRIM(@chrRowHead) +
CASE @intRowType
WHEN 1 THEN ')'
ELSE ''
END + ' from ' + @chrSource
--PRINT @chvExec
EXEC(@chvExec)
/*
Would be nice if you could use this value to define the crosstable
but this table must be created in a non-dynamic fashion.
*/
SELECT @intMaxRowHead=
(SELECT MAX(DATALENGTH(RTRIM(rowname))) FROM #rownames)
/* Create crosstable */
/* Define crosstable with rowhead field */
CREATE TABLE crosstable (rowhead varchar(255) NULL)
/* Alter crosstable by adding columns based on #colnames data */
DECLARE colname_cursor2 CURSOR FOR
SELECT colname FROM #colnames
OPEN colname_cursor2
FETCH colname_cursor2 INTO @chvCol
WHILE @@fetch_status >= 0
BEGIN
SELECT @chvColTemp = ''
IF @chvCol LIKE '%[^A-Z0-9]%'
BEGIN
SELECT @insR=1
WHILE @insR DATALENGTH(RTRIM(@chvCol))
BEGIN
SELECT @chvColTemp = RTRIM(@chvColTemp) +
CASE
WHEN SUBSTRING(@chvCol,@insR,1) LIKE '[A-Z0-9_]'
THEN SUBSTRING(@chvCol,@insR,1)
ELSE ''
END
SELECT @insR = @insR + 1
END
SELECT @chvCol = @chvColTemp
END
SELECT @chvExec = 'alter table ' + user_name() + '.crosstable add '+
CASE
WHEN SUBSTRING(@chvCol,1,1) LIKE '[^1234567890]' THEN @chvCol
ELSE '_' + LTRIM(@chvCol)
END + ' ' + @chvColType + ' null default(0)'
--PRINT @chvExec
EXEC(@chvExec)
FETCH colname_cursor2 INTO @chvCol
END
CLOSE colname_cursor2
DEALLOCATE colname_cursor2
/* Add #rowhead data to crosstable */
SELECT @chvExec='insert ' + USER_NAME() + '.crosstable (rowhead) select rowname from #rownames'
--PRINT @chvExec
EXEC(@chvExec)
/*
Create cursor with @chvRowHead and @chvColHead groupings and @chvValue
aggregate
*/
SELECT @chvExec = 'declare colname_cursor3 cursor for select ' +
CASE @intRowType
WHEN 1 THEN 'convert(varchar(255),' + RTRIM(@chrRowHead) + ')'
ELSE RTRIM(@chrRowHead)
END + ',' +
CASE
WHEN @intTemp = 3 THEN
CASE
WHEN @inyGrouping IN (1,3) THEN 'datename(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 3 THEN 'month'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE CASE @inyGrouping
WHEN 2 THEN '''Week'
WHEN 4 THEN '''Quarter'
WHEN 5 THEN '''Year'
END + '_'' +'+ 'datename(' +
CASE @inyGrouping
WHEN 2 THEN 'week'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead) + ')'
END
ELSE CASE @intColType
WHEN 1 THEN 'convert(varchar(255),' + RTRIM(@chrColHead) + ')'
ELSE RTRIM(@chrColHead)
END
END + ',total = convert(varchar(255),' + RTRIM(@chvType) + '(' +
RTRIM(@chrValue) + ')) from ' + RTRIM(@chrSource) + ' group by ' +
RTRIM(@chrRowHead) + ',' +
CASE @intTemp
WHEN 3 THEN
CASE
WHEN @inyGrouping IN (1,3) THEN 'datename(' +
CASE @inyGrouping
WHEN 1 THEN 'weekday'
WHEN 3 THEN 'month'
END + ',' + RTRIM(@chrColHead) + ')'
ELSE CASE @inyGrouping
WHEN 2 THEN '''Week'
WHEN 4 THEN '''Quarter'
WHEN 5 THEN '''Year'
END + '_'' + ' + 'datename(' +
CASE @inyGrouping
WHEN 2 THEN 'week'
WHEN 4 THEN 'quarter'
WHEN 5 THEN 'year'
END + ',' + RTRIM(@chrColHead) + ')'
END
ELSE RTRIM(@chrColHead)
END
--PRINT @chvExec
EXEC(@chvExec)
/* Iterate through cursor and update crosstable */
BEGIN TRAN
OPEN colname_cursor3
FETCH colname_cursor3 INTO @chvRow,@chvCol,@chvVal
WHILE @@fetch_status >= 0
BEGIN
SELECT @chvColTemp = ''
IF @chvCol LIKE '%[^A-Z0-9]%'
BEGIN
SELECT @insR=1
WHILE @insR DATALENGTH(RTRIM(@chvCol))
BEGIN
SELECT @chvColTemp = RTRIM(@chvColTemp) +
CASE
WHEN SUBSTRING(@chvCol,@insR,1) LIKE '[A-Z0-9_]'
THEN SUBSTRING(@chvCol,@insR,1)
ELSE ''
END
SELECT @insR = @insR + 1
END
SELECT @chvCol = @chvColTemp
END
SELECT @chvExec = 'update ' + USER_NAME() + '.crosstable set ' +
CASE
WHEN SUBSTRING(@chvCol,1,1) LIKE '[^1234567890]' THEN @chvCol
ELSE '_' + LTRIM(@chvCol)
END + ' = ' +
CASE
WHEN @chvVal IS NULL THEN '0'
ELSE RTRIM(@chvVal)
END + ' where rowhead = '''
SELECT @chvRow =
CASE WHEN @chvRow IS NULL THEN 'NULL'
ELSE RTRIM(@chvRow)
END
SELECT @chvRowTemp = ''
IF @chvRow LIKE '%''%'
BEGIN
SELECT @insR = 1
WHILE @insR DATALENGTH(RTRIM(@chvRow)) - 1
BEGIN
SELECT @chvRowTemp = RTRIM(@chvRowTemp) +
CASE
WHEN SUBSTRING(@chvRow,@insR,1) LIKE '[^'']' THEN
SUBSTRING(@chvRow,@insR,1)
ELSE ''''''
END
SELECT @insR = @insR + 1
END
SELECT @chvRow = @chvRowTemp
END
SELECT @chvExec = @chvExec + @chvRow + ''''
--PRINT @chvExec
EXEC(@chvExec)
FETCH colname_cursor3 INTO @chvRow,@chvCol,@chvVal
END
CLOSE colname_cursor3
DEALLOCATE colname_cursor3
COMMIT TRAN
/* Send back the data from crosstable */
SET NOCOUNT OFF
SELECT @chvExec = 'select * from ' + USER_NAME() + '.crosstable'
--PRINT @chvExec
EXEC(@chvExec)
/* Drop the tables */
DROP TABLE #colnames
DROP TABLE #rownames
DROP TABLE crosstable
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
Go
---以下为交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name
---以下为交叉表的列数是不确定的
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject fro
CREATE procedure CorssTab
@strTabName as varchar(50) = 'Employees', --此处放表名
@strCol as varchar(50) = 'City', --表头分组依据字段
@strGroup as varchar(50) = 'TitleOfCourtesy',--分组字段
@strNumber as varchar(50) = 'ReportsTo', --被统计的字段
@strSum as varchar(10) = 'Sum' --运算方式
AS
DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标
begin
SET nocount ON
SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段
OPEN corss_cursor
while (0=0)
BEGIN
FETCH NEXT FROM corss_cursor --遍历游标,将列头信息放入变量@strTmpCol
INTO @strTmpCol
if (@@fetch_status0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN '' + @strTmpCol + '' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
END
SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾
EXECUTE(@strsql) --执行
IF @@error 0 RETURN @@error --如果出错,返回错误代码
CLOSE corss_cursor
DEALLOCATE corss_cursor RETURN 0 --释放游标,返回0表示成功
end
GO
几点说明:
a、这是一个通用存储过程,使用时@strTabName、@strCol、@strGroup、@strNumber、@strSum几个变量设置一下就可以用到其他表上,其中结果集的第二列我加了个合计列
b、为了测试方便,我在存储过程中设置了默认值,就是前面提到的Employees表,这样直接运行时就可以出来我上面提到的结果。
c、使用时,可以把上面的代码复制到企业管理器的查询设计界面Sql窗格,或者查询分析器里运行一下(注意正确选择NorthWind数据库),就可以生成一个存储过程:CorssTab,然后直接运行CorssTab,如果出现本文前面类似的窗格,就表示运行成功了。
d、假如用于其它表,首先需要在你的用户数据库里生成此存储过程(当然也可以放到Master里,然后再加个变量:@DataBase,赋值为数据库名称,然后在上面代码打开指定数据库,这样所有的数据库都可以调用它),当你调用时,采取以下格式:
CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, OrderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'
上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量,大家可以运行试一下效果,虽然列头显示的名称不恰当,但基本效果出来了,相信大家通过对我的代码再作简单修改,可以达到满意的交叉表效果。
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1573678
相关推荐
采用前后端分离的方式进行开发,实现了几种常用的文件上传功能。 前端采用 vue.js + plupload + element-ui 实现了文件在浏览器端的发送, 后端采用 spring boot + spring + spring mvc + mybatis 实现了文件在服务器...
之后会深入剖析数据系统中 结构化数据的存储技术,介绍阿里云Tablestore选择哪种设计理念来更好的满足数据系统 中对结构化数据存储的需求。 2. 数据系统架构 1. 核心组件 上图是一个比较典型的技术架构,包含应用系统...
爬虫(Web Crawler)是一种自动化程序,用于从互联网上收集信息。其主要功能是访问网页、提取数据并存储,以便后续分析或展示。爬虫通常由搜索引擎、数据挖掘工具、监测系统等应用于网络数据抓取的场景。 爬虫的...
常用的存储形式包括关系型数据库、NoSQL数据库、JSON文件等。 遵守规则: 为避免对网站造成过大负担或触发反爬虫机制,爬虫需要遵守网站的robots.txt协议,限制访问频率和深度,并模拟人类访问行为,如设置User-...
本文主要研究一般电子地图的组成,结构,设计与实现方法,总结电子地图的优 势所在。 关键词:电子地图;设计;实现 0 前言 随着计算机技术及互联网技术的发展, 一种新的地图产品——网络电子地图悄然出现, 它正改变...
异常表示程序运行过程中可能出现的非正常状态,运行时异常表示虚拟机的通常操作中可能遇到的异常,是一种常见运行错误。java编译器要求方法必须声明抛出可能发生的非运行时异常,但是并不要求必须声明抛出未被捕获的...
1.5 几种综合网络服务器系统的安装 14 1.5.1 XAMPP 14 1.5.2 WAMP 16 1.5.3 Appserv 17 1.5.4 EasyPHP 18 1.5.5 VertrigoServ 19 1.6 几种开发工具的介绍 20 1.6.1 Vi及Vim 20 1.6.2 Eclipse+PHPEclipse插件 21 ...
10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件的查询与删除 ...
10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件的查询与删除 ...
1.3.10 几个常用的数学函数 27 1.4 .NET的面向对象之门 27 1.4.1 继承——“子承父业” 28 1.4.2 委托——“任务书” 35 1.4.3 事件——“年终分红” 42 1.4.4 反射——“解剖” 49 1.5 .NET开发几把小刀 52 1.5.1 ...
1.3.10 几个常用的数学函数 27 1.4 .NET的面向对象之门 27 1.4.1 继承——“子承父业” 28 1.4.2 委托——“任务书” 35 1.4.3 事件——“年终分红” 42 1.4.4 反射——“解剖” 49 1.5 .NET开发几把小刀 52 1.5.1 ...
异常表示程序运行过程中可能出现的非正常状态,运行时异常表示虚拟机的通常操作中可能遇到的异常,是一种常见运行错误。java编译器要求方法必须声明抛出可能发生的非运行时异常,但是并不要求必须声明抛出未被捕获...
10.求以下表达式的值,写出您想到的一种或几种实现方法: 1-2+3-4+……+m [Page] 答: int Num = this.TextBox1.Text.ToString() ; int Sum = 0 ; for (int i = 0 ; i ; i++) { if((i%2) == 1) { Sum += i ; ...
10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件的查询...
10.7 几种备份与恢复方法的比较 第11章 控制文件及日志文件的管理 11.1 控制文件 11.1.1 控制文件概述 11.1.2 多路复用控制文件 11.1.3 控制文件的创建 11.1.4 控制文件的备份与恢复 11.1.5 控制文件...
1.3.2跨域交互即缓存处理方法 7 1.3.3页面自适应机制,即设备自适应与浏览器自适应机制 8 1.3.4 “认我测”质检服务平台的设计和实现 8 1.4 本文的结构安排 8 第二章 多窗口类浏览器设计 11 2.1 多窗口类浏览器需求...
14.4. 四、存储实现:put(key,vlaue) 38 14.5. 五、读取实现:get(key) 41 15. 一百万数据放Arraylist数组,怎么放? 在哪个代? 42 15.1.1. 调整数组容量 42 16. Hashmap和 concurrentHashmap除了线程安全 还有什么...
本章将要介绍 PHP与 XML的操作方法,并对几个常用的 XML类库做一些简要介绍。 1 XML简介 XML是“可扩展性标识语言(eXtensible Markup Language)”的缩写,是一种类似于 HTML的标记性语言。但是与 H
输入和输出参数及返回值:描述如何将 DataSet 表中列的内容映射到 DataAdapter 命令属性的输入和输出参数(包括如何从存储过程中检索返回值)。 自动生成的命令:描述如何使用 CommandBuilder 为具有单个表 SELECT ...
在写作方式上,本书以一种开创性的方式使理论与实践达到极好的平衡,不仅对理论知识进行了清晰而透彻的阐述,而且根据读者理解这些知识的需要,精心设计了106个完整(每个案例分为功能描述、实现代码、效果展示和...