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

T-SQL 经典多种方式行专列、列转行,多种方式分页及其分页的存储过程

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

) GO

INSERT INTO [Scores]

SELECT 100, '语文', 80 UNION SELECT 100, '数学', 75 UNION SELECT 100, '英语', 70 UNION SELECT 100, '生物', 85 UNION SELECT 101, '语文', 80 UNION SELECT 101, '数学', 90 UNION SELECT 101, '英语', 70 UNION SELECT 101, '生物', 85

CREATE TABLE [Student] (

[ID] INT IDENTITY(100,1), [StuName] NVARCHAR(30), [Sex] NVARCHAR(30), [Age] CHAR(2)

) GO

INSERT INTO [Student]

SELECT '张三', '男', 80 UNION SELECT '李四', '女', 75

两表的数据如下图：

--自增标识 --学号 --科目 --成绩

--自增标识，学号 --姓名 --性别

--年龄

T-SQL 经典多种方式行专列、列转行,多种方式分页及其分页的存储过程

2、通过CASE…WHEN 语句和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.[Scores] GROUP BY StuNo ORDER BY StuNo ASC 结果如下图：

3、通过 JOIN…ON 实现两表联接，显示出学生姓名

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)

T-SQL 经典多种方式行专列、列转行,多种方式分页及其分页的存储过程

GROUP BY StuName ORDER BY StuName ASC 结果如下图：

4、通过 PIVOT 实现行转列

SELECT

StuNo AS '学号', StuName AS '姓名', AVG(语文) AS '语文', AVG(数学) AS '数学', AVG(英语) AS '英语', AVG(生物) AS '生物' FROM [Scores] PIVOT(

AVG(Score) FOR Subject IN (语文,数学,英语,生物)

) AS NewScores

JOIN [Student] ON (NewScores.StuNo=Student.ID) GROUP BY NewScores.StuNo,StuName ORDER BY StuName ASC 结果如下图：

二、T-SQL列转行

1、创建数据表并插入 4 条数据

CREATE TABLE [StudentScores] (

[ID] INT IDENTITY(1,1),

--自增标识 --学号 --语文 --数学 --英语 --生物

[StuNo] INT, ) GO

[Chinese] NVARCHAR(30), [English] NVARCHAR(30),

[Biology] NVARCHAR(30)

[Mathematics] NVARCHAR(30),

T-SQL 经典多种方式行专列、列转行,多种方式分页及其分页的存储过程

INSERT INTO [StudentScores] SELECT 100, 80, 85, 75, 80 UNION SELECT 101, 90, 80, 70, 75 UNION SELECT 102, 95, 90, 80, 70 UNION SELECT 103, 60, 70, 80, 85 数据如下图：

2、通过 UNION ALL…MAX 实现列转行

SELECT StuNo, 'Chinese' AS Subject, MAX(Chinese) AS 'Score' FROM [StudentScores] GROUP BY [StuNo] UNION ALL

SELECT StuNo, 'Mathematics' AS Subject, MAX(Mathematics) AS 'Score' FROM [StudentScores] GROUP BY [StuNo] UNION ALL

SELECT StuNo, 'English' AS Subject, MAX(English) AS 'Score' FROM [StudentScores] GROUP BY [StuNo] UNION ALL

SELECT StuNo, 'Biology' AS Subject, MAX(Biology) AS 'Score' FROM [StudentScores] GROUP BY [StuNo] 结果如下图：

T-SQL 经典多种方式行专列、列转行,多种方式分页及其分页的存储过程

3、用 UNPIVOT 实现列转行

SELECT StuNo, Subject, Score FROM [StudentScores] UNPIVOT (

Score FOR Subject IN

([Chinese], [Mathematics], [English],) AS NewStudentScores

三、T-SQL 分页

1、创建数据库并插入 40000 条数据

CREATE TABLE [Pagin] ( [ID] INT IDENTITY(1,1),

--自增标识 [Number] INT, --编号 [Type] NVARCHAR(30),

--类型 [Count] INT

--数量

) GO

declare @i int set @i = 0 while(@i<10000) begin

) [Biology]

T-SQL 经典多种方式行专列、列转行,多种方式分页及其分页的存储过程

INSERT INTO [Pagin] SELECT 10000+@i, 'A类', 80+@i%5 INSERT INTO [Pagin] SELECT 10000+@i, 'B类', 60+@i%10 INSERT INTO [Pagin] SELECT 10000+@i, 'C类', 70+@i%8 INSERT INTO [Pagin] SELECT 10000+@i, 'D类', 90+@i%3 set @i = @i + 1

end

2、通过 TOP 实现分页

方案一：两次 TOP 实现，原型如下

SELECT * FROM (

SELECT TOP 5 * FROM (

SELECT TOP 25 * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC ) AS TEMPTABLE1 ORDER BY ID DESC SELECT TOP 5 * FROM (

SELECT TOP 25 * FROM [Pagin] WHERE ID>0 ORDER BY ID ASC ) AS TEMPTABLE1 ORDER BY ID DESC

) AS TEMPTABLE2 ORDER BY ID ASC

说明：第一个 TOP 表示页面容量，第二个 TOP 表示页面容量*当前页码数。 弊病： 1、强制排序，否则不能分页，虽然目前基本上查询表都要排序。

2、排序字段不能有空值即null，否则分页结果不符实际情况。 3、多 …… 此处隐藏：4002字，全部文档内容请下载后查看。喜欢就下载吧 ……