[转载]SQL语句行列转换(附带数据库、表、视图操作)

[转载]SQL语句行列转换(附带数据库、表、视图操作) – 温度 – 博客园.

本来只是想解决怎么把数据的行和列进行转换的,但最近觉得一些数据库SQL语句的操作,很久没用了,有点陌生。所以也就随笔记录一些简单但很基本的操作。

 

我的数据库是MSSQL2005.

 

第一部分主要的操作包含:数据库的创建、删除,表的增、删、改,表中数据的增、删、改、查,视图的操作。

 

 1 --查询数据库是否存在
 2 if exists ( select * from sysdatabases where [name]='TestDB')
 3 print 'Yes, the DB exists'
 4 else
 5 print 'No, need a new one?'
 6 
 7 --新建一个数据库
 8 create database TestDB on
 9 (
 10     name = 'TestData',
 11     filename = 'G:\DBS\KeyTest.mdf',
 12     size = 3,
 13     filegrowth = 2
 14 )
 15 log on
 16 (
 17     name = 'TestLog',
 18     filename = 'G:\DBS\KeyTest.ldf',
 19     size = 3,
 20     filegrowth = 10
 21 )
 22 
 23 --drop database TestDB
 24 
 25 use TestDB
 26 go
 27 
 28 --新建一个表
 29 create table [Scores]
 30 (
 31     [ID] int identity(1,1) primary key,
 32     [Student] varchar(20) ,
 33     [Subject] varchar(30),
 34     [Score] float
 35 )
 36 
 37 --drop table [Scores]
 38 
 39 --修改表中的一列
 40 alter table Scores alter column [Student] varchar(20) not null
 41 
 42 --新增一列
 43 alter table Scores add Birthday datetime 
 44 
 45 --删除一列
 46 alter table Scores drop column Birthday
 47 
 48 --往表中插入单条数据,方法1:带列名
 49 insert into Scores(Student,Subject,Score)
 50 values('张三','语文','90')
 51 
 52 --往表中插入单条数据,方法2:不带列名,但要求值的类型要和列字段类型对应
 53 insert into Scores
 54 values('张三','英语','95')
 55 
 56 --插入多条数据:用union或者union all
 57 insert into Scores(Student,Subject,Score)
 58 select '李四','语文','89'
 59 union all
 60 select '李四','英语','78'
 61 
 62 --删除表中数据,没有条件时,删除所有
 63 delete from Scores where ID in(7,8)
 64 
 65 --修改表中数据
 66 update Scores 
 67 set Student='王五',Score='94'
 68 where ID=10
 69 
 70 --查看数据
 71 select * from Scores
 72 
 73 --查看表中最大的identity值
 74 select @@identity
 75 
 76 --或者利用dbcc命令查看表中最大的identity值
 77 dbcc checkident('Scores',noreseed)
 78 
 79 --创建视图,全部省略视图的属性列名,由子查询目标列的字段组成
 80 create view StudentView
 81 as
 82 select Student,Subject,Score
 83 from Scores
 84 
 85 --加上with check option,以后对视图的操作(增,改,删,查)都会自动加上where ID>3
 86 /*
 87 create view StudentView
 88 as
 89 select Student,Subject,Score
 90 from Scores
 91 where ID>3
 92 with check option
 93 */
 94 
 95 --创建视图,全部定义属性列名,需要定义列名的情况:
 96 ----某个目标列(子查询)不是单纯的属性列,而是聚集函数或列表达式
 97 ----多表连接时选出了几个同名列
 98 ----需要在视图中为某个列启用新的更合适的名字
 99 create view IS_Student(Student,Subject,MaxScore)
100 as
101 select Student,Subject,Score
102 from Scores
103 where Score=(select max(Score) from Scores)
104 
105 
106 --查询视图,和基本表完全样,只不过如果视图中有with check option,会自动加上那个条件
107 select * 
108 from StudentView
109 
110 --查询自定义列名的视图
111 select * 
112 from IS_Student
113 
114 --对视图的insert/delete/update,和对基本表的操作一样,并且最终都是用RDBMS自动转换为对基本表的更新
115 --并不是所有的视图都是可更新的,因为有些视图的更新不能有意义的转换成对相应基本表的更新
116 
117 --删除视图
118 drop view StudentView

 

第二部分,这次练习的主题。

 

【一】行转列

 

1,查询原始的数据

 

/***这次练习的主题,行转列,列转行***/
select * from Scores

 

 

2,得到姓名,通过group by

 

select Student as '姓名'
from Scores
group by Student
order by Student

 

 

3,再加上max, case……when

 

select Student as '姓名',
max(case Subject when '语文' then Score else 0 end) as '语文' ,--如果这个行是“语文”,就选此行作为列
max(case Subject when '英语' then Score else 0 end ) as '英语'
from Scores
group by Student
order by Student

 

 

 

 

查看其它资料时,看到另外一种方法,用pivot

 

--group by, avg/max, pivot。这里用max和avg,结果都一样,有什么区别吗?有点不明白
--参考网上的资料,用法如下
/*
pivot(
  聚合函数(要转成列值的列名)
  for 要转换的列
  in(目标列名)
  )
*/
select Student as '姓名',
avg(语文) as '语文',
avg(英语) as '英语'
from Scores
pivot(
    avg(Score) for Subject 
    in (语文,英语)
    )as NewScores
group by Student
order by Student asc

 

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏