T-SQL经典行专列、列转行,分页及存储过程.doc

T-SQL 经典编程说明：本实例是以 SQL Server 2005 为运行环境的。准备工作：创建一个名为 DB 的数据库（CREATE DATABASE DB）。一、T-SQL 行转列1、创建如下表CREATE TABLE Scores ( ID INT IDENTITY(1,1),-自增标识 StuNo INT,-学号 Subject NVARCHAR(30),-科目 Score FLOAT-成绩)GOINSERT INTO ScoresSELECT 100, 语文, 80 UNIONSELECT 100, 数学, 75 UNIONSELECT 100, 英语, 70 UNIONSELECT 100, 生物, 85 UNION SELECT 101, 语文, 80 UNIONSELECT 101, 数学, 90 UNIONSELECT 101, 英语, 70 UNIONSELECT 101, 生物, 85 CREATE TABLE Student ( ID INT IDENTITY(100,1), -自增标识，学号 StuName NVARCHAR(30), -姓名 Sex NVARCHAR(30),-性别 Age CHAR(2)-年龄)GOINSERT INTO StudentSELECT 张三, 男, 80 UNIONSELECT 李四, 女, 75两表的数据如下图：2、通过CASEWHEN 语句和GROUP BY聚合函数 来实现行转列SELECT StuNo AS 学号, MAX(CASE Subject WHEN 语文 THEN Score ELSE 0 END) AS 语文, MAX(CASE Subject WHEN 数学 THEN Score ELSE 0 END) AS 数学, MAX(CASE Subject WHEN 英语 THEN Score ELSE 0 END) AS 英语, MAX(CASE Subject WHEN 生物 THEN Score ELSE 0 END) AS 生物, SUM(Score) AS 总分, AVG(Score) AS 平均分FROM dbo.ScoresGROUP BY StuNoORDER BY StuNo ASC结果如下图：3、通过 JOINON 实现两表联接，显示出学生姓名SELECT MAX(StuNo) AS 学号, StuName AS 姓名, MAX(CASE Subject WHEN 语文 THEN Score ELSE 0 END) AS 语文, MAX(CASE Subject WHEN 数学 THEN Score ELSE 0 END) AS 数学, MAX(CASE Subject WHEN 英语 THEN Score ELSE 0 END) AS 英语, MAX(CASE Subject WHEN 生物 THEN Score ELSE 0 END) AS 生物, SUM(Score) AS 总分, AVG(Score) AS 平均分FROM dbo.Scores A join Student B on (A.StuNo=B.ID)GROUP BY StuNameORDER BY StuName ASC结果如下图：4、通过 PIVOT 实现行转列SELECTStuNo AS 学号,StuName AS 姓名,AVG(语文) AS 语文,AVG(数学) AS 数学,AVG(英语) AS 英语,AVG(生物) AS 生物FROM ScoresPIVOT(AVG(Score) FOR Subject IN(语文,数学,英语,生物) AS NewScoresJOIN Student ON (NewScores.StuNo=Student.ID)GROUP BY NewScores.StuNo,StuNameORDER BY StuName ASC结果如下图：二、T-SQL列转行1、创建数据表并插入 4 条数据CREATE TABLE StudentScores ( ID INT IDENTITY(1,1),-自增标识 StuNo INT,-学号 Chinese NVARCHAR(30),-语文 Mathematics NVARCHAR(30),-数学 English NVARCHAR(30),-英语 Biology NVARCHAR(30)-生物)GOINSERT INTO StudentScoresSELECT 100, 80, 85, 75, 80 UNIONSELECT 101, 90, 80, 70, 75 UNIONSELECT 102, 95, 90, 80, 70 UNIONSELECT 103, 60, 70, 80, 85数据如下图：2、通过 UNION ALLMAX 实现列转行SELECT StuNo, Chinese AS Subject, MAX(Chinese) AS Score FROM StudentScores GROUP BY StuNoUNION ALLSELECT StuNo, Mathematics AS Subject, MAX(Mathematics) AS Score FROM StudentScores GROUP BY StuNoUNION ALLSELECT StuNo, English AS Subject, MAX(English) AS Score FROM StudentScores GROUP BY StuNoUNION ALLSELECT StuNo, Biology AS Subject, MAX(Biology) AS Score FROM StudentScores GROUP BY StuNo结果如下图：3、用 UNPIVOT 实现列转行SELECT StuNo, Subject, ScoreFROM StudentScoresUNPIVOT( Score FOR Subject IN (Chinese, Mathematics, English, Biology) AS NewStudentScores三、T-SQL 分页1、创建数据库并插入 40000 条数据CREATE TABLE Pagin ( ID INT IDENTITY(1,1),-自增标识 Number INT,-编号 Type NVARCHAR(30),-类型 Count INT-数量)GOdeclare i intset i = 0while(i0 ORDER BY ID ASC) AS TEMPTABLE1 ORDER BY ID DESC ) AS TEMPTABLE2 ORDER BY ID ASCSELECT TOP 5 * FROM (SELECT TOP 25 * FROM Pagin WHERE ID0 ORDER BY ID ASC) AS TEMPTABLE1 ORDER BY ID DESC说明：第一个 TOP 表示页面容量，第二个 TOP 表示页面容量*当前页码数。弊病：1、强制排序，否则不能分页，虽然目前基本上查询表都要排序。2、排序字段不能有空值即null，否则分页结果不符实际情况。3、多次order by 速度会快吗，有待我进一步大数据量测试。方案二：两次 TOP 基于NOT IN 实现，原型如下select top 5 * from Pagin where ID not in (select top 25 ID from Pagin order by ID)order by ID说明：第一个 TOP 表示页面容量，第二个 TOP 表示页面容量*当前页码数。弊病：1、强制排序。2、排序列必须是唯一列，否则分页情况不符实际。3、使用not in,速度慢。方案三：两次 TOP 基于MAX 或 MIN 实现，原型如下select top 5 * from Paginwhere ID (select max(p.ID) from (select top 25 ID from Pagin order by id) as p)order by ID说明：第一个 TOP 表示页面容量，第二个 TOP 表示页面容量*当前页码数。弊病：1、强制排序。2、排序列必须是唯一列,否则分页情况不符实际。最后总结：在 sqlserver 分页中，第二第三种方案基本上是淘汰掉的，因为现在基本上什么表都是根据添加时间来排序，所以那两种方案没有用，真亏作者也敢发布出来，只有第一种方案还是稍微能用一下，但还是要复杂的拼sql 语句，不方便，要通用于所有表有点难度，象oracle 就很方便了，基于rownum ，传入一个sql 查询语句，这个查询语句爱怎么写就怎么写，反正保证它得到一个结果集就行，不像sqlserver又是要求唯一健又是要求必须排序，把一个结果集颠来倒去，不慢才怪呢。方案一的简单存储过程如下：CREATE PROCEDURE proc_pagepageIndex INT = 0,-页索引pageSize INT = 10,-页大小recordCount INT = 0 OUTPUT,-返回纪录总数pageCount INT = 0 OUTPUT-返回页总数ASDECLARE sql NVARCHAR(1300)-主sql语句-得到记录总数-BEGINDECLARE recordTotal INTSET sql = NSELECT recordTotal=COUNT(ID) FROM Pagin WHERE ID0EXEC SP_EXECUTESQL sql,NrecordTotal INT OUTPUT,recordTotal OUTPUT -recordTotal = recordCount OUTPUTSET recordCount = recordTotalEND-计算页总数-IF(recordCount%pageSize=0)-如果记录总数除以页大小的余数为零SET pageCount = recordCount/pageSizeELSE-如果记录总数除以页大小的余数不为零SET pageCount = recordCount/pageSize + 1-根据页索引执行分页查询-IF(pageIndexpageCount)-如果是第一页，或者该页不存在，则默认也索引为1，即第一页BEGINSET pageIndex = 1SET sql = SELECT TOP +STR(pageSize)+ * FROM Pagin WHERE ID0 ORDER BY ID ASCEXEC SP_EXECUTESQL sqlENDELSE-如果不是第一页，即其它页BEGINSET sql = SELECT * FROM (SELECT TOP +STR(pageSize)+ * FROM (SELECT TOP +STR(pageSize*pageIndex)+ * FROM Pagin WHERE ID0 ORDER BY ID ASC) AS TB1 ORDER BY ID