SQL实现交叉表的方法
来源:互联网 发布:单片机湿度程序 编辑:程序博客网 时间:2024/06/08 18:33
交叉一般来讲是分组统计的一种,形式更复杂,显示更清淅,但数据库本身并没有提供实现交叉表的功能,自己创建交叉表不仅要对过程、游标、临时表、动态SQL等非常熟悉,而且思路也要清淅,本例以PUBS.DBO.SALES表的数据做样本:
CREATE PROCEDURE UP_TEST(
@T1 VARCHAR(30),@T2 VARCHAR(30),
@T3 VARCHAR(30),@T4 VARCHAR(30)) AS
--T1 表名,T2,T3是交叉表的两上分类字段,T4是汇总字段
--T2是行字段,T3列字段
BEGIN
DECLARE @SQL VARCHAR(7999),@FIELD VARCHAR(30)
SELECT @SQL='SELECT DISTINCT ' @T3 ' FROM ' @T1
CREATE TABLE #FIELD(FIELD VARCHAR(30))
--将列字段提取到临时表#FIELD中
INSERT INTO #FIELD EXEC(@SQL)
SELECT @SQL='CREATE TABLE CROSS_TEST(' @T2 ' VARCHAR(30),'
DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT * FROM #FIELD
OPEN CUR_FIELD
FETCH CUR_FIELD INTO @FIELD
WHILE @@FETCH_STATUS=0 BEGIN
SELECT @FIELD='[' @FIELD ']'
SELECT @SQL=@SQL @FIELD ' DECIMAL(8,2) DEFAULT 0,'
FETCH CUR_FIELD INTO @FIELD
END
SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1) ')'
--创建临时交叉表CROSS_TEST
EXEC(@SQL)
SELECT @SQL='INSERT INTO CROSS_TEST(' @T2 ') SELECT DISTINCT ' @T2 ' FROM ' @T1
--将行数据存入交叉表#CROSS_TEST
EXEC(@SQL)
--创建分组数据表TEMP
SELECT @SQL='CREATE TABLE TEMP(' @T2 ' VARCHAR(30),' @T3 ' VARCHAR(30),' @T4 ' DECIMAL(8,2))'
EXEC(@SQL)
--将交叉汇总数据放入交叉表
SELECT @SQL='SELECT ' @T2 ',' @T3 ', SUM(QTY) QTY FROM ' @T1 ' GROUP BY ' @T2 ',' @T3
INSERT INTO TEMP EXEC(@SQL)
--将汇总数据写入交叉表
DECLARE CUR_SUM CURSOR LOCAL FOR SELECT * FROM TEMP
DECLARE @F1 VARCHAR(30),@F2 VARCHAR(30),@QTY DECIMAL(8,2),@Q1 VARCHAR(30)
OPEN CUR_SUM
FETCH CUR_SUM INTO @F1,@F2,@QTY
WHILE @@FETCH_STATUS=0 BEGIN
SELECT @F2='[' @F2 ']',@Q1=CAST(@QTY AS VARCHAR(30))
SELECT @SQL='UPDATE CROSS_TEST SET ' @F2 '=' @Q1 ' WHERE ' @T2 '=''' @F1 ''''
EXEC(@SQL)
FETCH CUR_SUM INTO @F1,@F2,@QTY
END
CLOSE CUR_SUM
SELECT * FROM CROSS_TEST
DROP TABLE TEMP
DROP TABLE CROSS_TEST
DROP TABLE #FIELD
END
--------------------------------------------------------
EXEC UP_TEST 'SALES','TITLE_ID','STOR_ID','QTY'
说明:字段加中括号为了处理字段中含有特殊字符,值得注意得是要实现交叉表的表必须有两个分类,本例只支持分类字段的数据类型是字符型的,最大的问题就是高亮显示这行的WHERE条件啦,字符类型字段查询时条件必须加单引号,如果是数值类型就可以直接写,所以数值类型的分类字段更容易实现一些,更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息,本例就没有实现,最后一点工作大家自己练练手吧。
CREATE PROCEDURE UP_TEST(
@T1 VARCHAR(30),@T2 VARCHAR(30),
@T3 VARCHAR(30),@T4 VARCHAR(30)) AS
--T1 表名,T2,T3是交叉表的两上分类字段,T4是汇总字段
--T2是行字段,T3列字段
BEGIN
DECLARE @SQL VARCHAR(7999),@FIELD VARCHAR(30)
SELECT @SQL='SELECT DISTINCT ' @T3 ' FROM ' @T1
CREATE TABLE #FIELD(FIELD VARCHAR(30))
--将列字段提取到临时表#FIELD中
INSERT INTO #FIELD EXEC(@SQL)
SELECT @SQL='CREATE TABLE CROSS_TEST(' @T2 ' VARCHAR(30),'
DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT * FROM #FIELD
OPEN CUR_FIELD
FETCH CUR_FIELD INTO @FIELD
WHILE @@FETCH_STATUS=0 BEGIN
SELECT @FIELD='[' @FIELD ']'
SELECT @SQL=@SQL @FIELD ' DECIMAL(8,2) DEFAULT 0,'
FETCH CUR_FIELD INTO @FIELD
END
SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1) ')'
--创建临时交叉表CROSS_TEST
EXEC(@SQL)
SELECT @SQL='INSERT INTO CROSS_TEST(' @T2 ') SELECT DISTINCT ' @T2 ' FROM ' @T1
--将行数据存入交叉表#CROSS_TEST
EXEC(@SQL)
--创建分组数据表TEMP
SELECT @SQL='CREATE TABLE TEMP(' @T2 ' VARCHAR(30),' @T3 ' VARCHAR(30),' @T4 ' DECIMAL(8,2))'
EXEC(@SQL)
--将交叉汇总数据放入交叉表
SELECT @SQL='SELECT ' @T2 ',' @T3 ', SUM(QTY) QTY FROM ' @T1 ' GROUP BY ' @T2 ',' @T3
INSERT INTO TEMP EXEC(@SQL)
--将汇总数据写入交叉表
DECLARE CUR_SUM CURSOR LOCAL FOR SELECT * FROM TEMP
DECLARE @F1 VARCHAR(30),@F2 VARCHAR(30),@QTY DECIMAL(8,2),@Q1 VARCHAR(30)
OPEN CUR_SUM
FETCH CUR_SUM INTO @F1,@F2,@QTY
WHILE @@FETCH_STATUS=0 BEGIN
SELECT @F2='[' @F2 ']',@Q1=CAST(@QTY AS VARCHAR(30))
SELECT @SQL='UPDATE CROSS_TEST SET ' @F2 '=' @Q1 ' WHERE ' @T2 '=''' @F1 ''''
EXEC(@SQL)
FETCH CUR_SUM INTO @F1,@F2,@QTY
END
CLOSE CUR_SUM
SELECT * FROM CROSS_TEST
DROP TABLE TEMP
DROP TABLE CROSS_TEST
DROP TABLE #FIELD
END
--------------------------------------------------------
EXEC UP_TEST 'SALES','TITLE_ID','STOR_ID','QTY'
说明:字段加中括号为了处理字段中含有特殊字符,值得注意得是要实现交叉表的表必须有两个分类,本例只支持分类字段的数据类型是字符型的,最大的问题就是高亮显示这行的WHERE条件啦,字符类型字段查询时条件必须加单引号,如果是数值类型就可以直接写,所以数值类型的分类字段更容易实现一些,更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息,本例就没有实现,最后一点工作大家自己练练手吧。
- SQL实现交叉表的方法
- SQL实现交叉表的方法
- sql中交叉表的实现
- 交叉表的sql实现(转)
- SQL交叉报表的实现
- Sql交叉表简单实现
- 交叉表的实现
- 交叉表的实现,
- sql 行列转化/交叉报表的实现
- 水晶报表中几种交叉表的实现方法
- 转 :水晶报表中几种交叉表的实现方法
- 水晶报表中几种交叉表的实现方法
- 水晶报表中几种交叉表的实现方法
- [SQL]交叉表的处理
- SQL交叉表的实例
- 应用SQL交叉表实现行列转换
- SQl 实现动态交叉表查询
- SQL交叉表查询 如何实现
- 我有个datawindow 有三个参数传递的,我想当我传递三个新的参数的时候 可不可以保留原来的数据
- 如何编写Linux设备驱动程序
- 我要“植树造林”
- asp:限制重复的次数
- 对c++关键字的思考
- SQL实现交叉表的方法
- RHEL5问题
- ConcurrentHashMap应用注意事项
- 常量和静态只读字段
- 业务对象的特征和类型
- 如何使用XCDZIP35.OCX对文件压缩-已解决整理
- 伪指令.word
- 在Windows Visual C++ 6.0/7.0下编译带ssl的curl库
- 开博