如何使用SQL语句将行和列进行转换
来源:互联网 发布:hive group by 优化 编辑:程序博客网 时间:2024/06/10 16:16
数据库是MSSQL2005.
第一部分主要的操作包含:数据库的创建、删除,表的增、删、改,表中数据的增、删、改、查,视图的操作。
--查询数据库是否存在
if exists ( select * from sysdatabases where [name]='TestDB')
print 'Yes, the DB exists'
else
print 'No, need a new one?'
--新建一个数据库
create database TestDB on
(
name = 'TestData',
filename = 'G:\DBS\KeyTest.mdf',
size = 3,
filegrowth = 2
)
log on
(
name = 'TestLog',
filename = 'G:\DBS\KeyTest.ldf',
size = 3,
filegrowth = 10
)
--drop database TestDB
use TestDB
go
--新建一个表
create table [Scores]
(
[ID] int identity(1,1) primary key,
[Student] varchar(20) ,
[Subject] varchar(30),
[Score] float
)
--drop table [Scores]
--修改表中的一列
alter table Scores alter column [Student] varchar(20) not null
--新增一列
alter table Scores add Birthday datetime
--删除一列
alter table Scores drop column Birthday
--往表中插入单条数据,方法1:带列名
insert into Scores(Student,Subject,Score)
values('张三','语文','90')
--往表中插入单条数据,方法2:不带列名,但要求值的类型要和列字段类型对应
insert into Scores
values('张三','英语','95')
--插入多条数据:用union或者union all
insert into Scores(Student,Subject,Score)
select '李四','语文','89'
union all
select '李四','英语','78'
--删除表中数据,没有条件时,删除所有
delete from Scores where ID in(7,8)
--修改表中数据
update Scores
set Student='王五',Score='94'
where ID=10
--查看数据
select * from Scores
--查看表中最大的identity值
select @@identity
--或者利用dbcc命令查看表中最大的identity值
dbcc checkident('Scores',noreseed)
--创建视图,全部省略视图的属性列名,由子查询目标列的字段组成
create view StudentView
as
select Student,Subject,Score
from Scores
--加上with check option,以后对视图的操作(增,改,删,查)都会自动加上where ID>3
/*
create view StudentView
as
select Student,Subject,Score
from Scores
where ID>3
with check option
*/
--创建视图,全部定义属性列名,需要定义列名的情况:
----某个目标列(子查询)不是单纯的属性列,而是聚集函数或列表达式
----多表连接时选出了几个同名列
----需要在视图中为某个列启用新的更合适的名字
create view IS_Student(Student,Subject,MaxScore)
as
select Student,Subject,Score
from Scores
where Score=(select max(Score) from Scores)
--查询视图,和基本表完全样,只不过如果视图中有with check option,会自动加上那个条件
select *
from StudentView
--查询自定义列名的视图
select *
from IS_Student
--对视图的insert/delete/update,和对基本表的操作一样,并且最终都是用RDBMS自动转换为对基本表的更新
--并不是所有的视图都是可更新的,因为有些视图的更新不能有意义的转换成对相应基本表的更新
--删除视图
drop view StudentView
if exists ( select * from sysdatabases where [name]='TestDB')
print 'Yes, the DB exists'
else
print 'No, need a new one?'
--新建一个数据库
create database TestDB on
(
name = 'TestData',
filename = 'G:\DBS\KeyTest.mdf',
size = 3,
filegrowth = 2
)
log on
(
name = 'TestLog',
filename = 'G:\DBS\KeyTest.ldf',
size = 3,
filegrowth = 10
)
--drop database TestDB
use TestDB
go
--新建一个表
create table [Scores]
(
[ID] int identity(1,1) primary key,
[Student] varchar(20) ,
[Subject] varchar(30),
[Score] float
)
--drop table [Scores]
--修改表中的一列
alter table Scores alter column [Student] varchar(20) not null
--新增一列
alter table Scores add Birthday datetime
--删除一列
alter table Scores drop column Birthday
--往表中插入单条数据,方法1:带列名
insert into Scores(Student,Subject,Score)
values('张三','语文','90')
--往表中插入单条数据,方法2:不带列名,但要求值的类型要和列字段类型对应
insert into Scores
values('张三','英语','95')
--插入多条数据:用union或者union all
insert into Scores(Student,Subject,Score)
select '李四','语文','89'
union all
select '李四','英语','78'
--删除表中数据,没有条件时,删除所有
delete from Scores where ID in(7,8)
--修改表中数据
update Scores
set Student='王五',Score='94'
where ID=10
--查看数据
select * from Scores
--查看表中最大的identity值
select @@identity
--或者利用dbcc命令查看表中最大的identity值
dbcc checkident('Scores',noreseed)
--创建视图,全部省略视图的属性列名,由子查询目标列的字段组成
create view StudentView
as
select Student,Subject,Score
from Scores
--加上with check option,以后对视图的操作(增,改,删,查)都会自动加上where ID>3
/*
create view StudentView
as
select Student,Subject,Score
from Scores
where ID>3
with check option
*/
--创建视图,全部定义属性列名,需要定义列名的情况:
----某个目标列(子查询)不是单纯的属性列,而是聚集函数或列表达式
----多表连接时选出了几个同名列
----需要在视图中为某个列启用新的更合适的名字
create view IS_Student(Student,Subject,MaxScore)
as
select Student,Subject,Score
from Scores
where Score=(select max(Score) from Scores)
--查询视图,和基本表完全样,只不过如果视图中有with check option,会自动加上那个条件
select *
from StudentView
--查询自定义列名的视图
select *
from IS_Student
--对视图的insert/delete/update,和对基本表的操作一样,并且最终都是用RDBMS自动转换为对基本表的更新
--并不是所有的视图都是可更新的,因为有些视图的更新不能有意义的转换成对相应基本表的更新
--删除视图
drop view StudentView
第二部分,这次练习的主题。
【一】行转列
1,查询原始的数据
/***这次练习的主题,行转列,列转行***/
select * from Scores
select * from Scores
2,得到姓名,通过group by
select Student as '姓名'
from Scores
group by Student
order by Student
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
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 Scorespivot( avg(Score) for Subject in (语文,英语) )as NewScoresgroup by Studentorder by Student asc
- 如何使用SQL语句将行和列进行转换
- sql语句将行列进行转换
- SQL语句如何使用日期作为列
- Oracle行转换列SQL语句
- SQL转换行和列
- sql如何将列转化为行
- SQL的行和列转换
- PL/SQL 使用DDL语句创建列和键
- 如何使用SQL语句进行简单的数据库操作?
- ms sql 将列转换成行
- PowerDesigner16.5导出SQL语句时如何将name列复制到comment注释里面
- C#将SQL语句转换为分页SQL和获取记录数SQL
- 数据库中用sql语句进行行列转换
- Excel中如何将数字列转换成文本列
- 将linq实时转换为SQL语句
- Sql查询语句将数据转换数据格式
- 使用 WMSYS.WM_CONCAT 进行列转换
- 如何对于几百行SQL语句进行优化?
- SupeSite模块参数说明
- MD5算法实现
- vim全选命令解析
- lucene学习。
- Access数据库中Group by 语句特别之处
- 如何使用SQL语句将行和列进行转换
- spring--IoC和DI
- 约瑟夫排序
- createfile
- Android 菜单(OptionMenu)大全
- DOS命令之mem
- jsfl 批量设置bitmap的链接类
- 获得json数据的id或者说是key
- java.lang.OutOfMemoryError: GC overhead limit exceeded解决办法