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 --成绩
)
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通过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.[Scores]
GROUP BY StuNo
ORDER BY StuNo ASCSELECT
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 StuName
ORDER BY StuName ASC
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, --学号
[Chinese] NVARCHAR(30), --语文
[Mathematics] NVARCHAR(30),--数学
显示全部