交差表查询(SQL SERVER和Oralce)
什么是交差表查询
假设原表如下：
Mobileid parameter value
---------------------------------------------------------------_
诺基亚N71 外观样式 直板
诺基亚N71 重量 95G
三星D828 外观样式 滑盖
三星D828 重量 80G
想要显示成如下结构：
Mobileid 外观样式 重量
---------------------------------------------------------------
诺基亚N71 直板 95G
三星D828 滑盖 80G
这就是交差表查询。
SQL SERVER中交差表的实现
--注：SQL SERVER中的代码不是我的原创，是从网上找到的，经调试可正常运行
所用数据库：NORTHWIND
需要借助于CASE END语句
静态交差表
SELECT TitleOfCourtesy,
SUM(CASE City WHEN 'London' THEN ReportsTo ELSE NULL END) AS [London City],
SUM(CASE City WHEN 'Redmond' THEN ReportsTo ELSE NULL END) AS [Redmond City],
SUM(CASE City WHEN 'Seattle' THEN ReportsTo ELSE NULL END) AS [Seattle City]
FROM Employees
GROUP BY TitleOfCourtesy
另一个例子：
CREATE TABLE t_emp --员工表
(
empid INT PRIMARY KEY,
empname VARCHAR(20) NOT NULL,
deptid INT --部门编号
)
GO
INSERT INTO t_emp VALUES(1,'张三',100)
INSERT INTO t_emp VALUES(2,'李四',100)
INSERT INTO t_emp VALUES(3,'王五',200)
INSERT INTO t_emp VALUES(4,'马六',200)
INSERT INTO t_emp VALUES(5,'陈七',200)
go
假如想查询每个部门的人数，输出结果为：
100 200
---------------
2 3
--静态交差表查询
SELECT SUM(CASE deptid WHEN 100 THEN 1 END) '100',
SUM(CASE deptid WHEN 200 THEN 1 END) '200'
FROM t_emp
实现动态交差表的通用存储过程
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 INTO @strTmpCol --遍历游标，将列头信息放入变量@strTmpCol
if (@@fetch_status<>0)
break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
END
--CLOSE CORSS_CURSOR
--DEALLOCATE CORSS_CURSOR
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
--使用员工表employees验证存储过程
EXEC CORSSTAB--使用订单表orders验证存储过程
CorssTab @strTabName = 'Orders', @strCol = 'DATEPART(yy, OrderDate)',@strGroup = 'CustomerID', @strNumber = 'OrderID', @strSum = 'Count'
Oracle中交差表的实现
CONN scott/tiger
静态交差表
SELECT deptno,SUM(DECODE(job,'CLERK',1,0)) AS CLERK,
SUM(DECODE(job,'SALESMAN',1,0)) AS SALESMAN
FROM emp
GROUP BY deptno;
--查询结果
DEPTNO CLERK SALESMAN---------- ---------- ------------
30 1 4
20 2 0
10 1 0
动态交叉表
--不好意思，没有写通用的，但原理上和SQL的差不多
CREATE OR REPLACE PROCEDURE sp_crosstable
(o_cur OUT SYS_REFCURSOR)
AS
l_sqlstr VARCHAR2(1000);
CURSOR cur_temp IS SELECT DISTINCT job FROM emp;
BEGIN
l_sqlstr:='SELECT deptno';
FOR cur IN cur_temp
LOOP
l_sqlstr:=l_sqlstr||',SUM(DECODE(job,'''||cur.job||''',1,0)) AS '||cur.job;
END LOOP;
l_sqlstr:=l_sqlstr||' FROM emp GROUP BY deptno';
OPEN o_cur FOR l_sqlstr;
END sp_crosstable;
/
--使用以下代码测试存储过程
SQL> VAR cur REFCURSOR
SQL> EXEC sp_crosstable(:cur);
PL/SQL 过程已成功完成。
SQL> SET SERVEROUT ON
SQL> PRINT cur
DEPTNO CLERK SALESMAN PRESIDENT MANAGER ANALYST
---------- ---------- ---------- ---------- ---------- ----------
30 1 4 0 1 0
20 2 0 0 1 2
10 1 0 1 1 0
