行列转行
来源:互联网 发布:手机翻译软件哪个好 编辑:程序博客网 时间:2024/04/30 01:51
create database Hj
go
use hj
go
create table Teacher --教师表
(
tid int primary key identity,
tname varchar(20)
)
go
create table Subject --科目表
(
sjid int primary key, ,
sjname varchar(20),
sjtid int foreign key references Teacher(tid),--对应相应的老师
)
go
create table Student --学生表
(
sid int primary key identity,
sname varchar(20),
)
go
create table Achievement --成绩表
(
aid int primary key identity,
asid int foreign key references student(sid),--对应的学生
asjid int foreign key references Subject(sjid),--对应的课程
ascore float --相应课程的分数
)
--插入数据
--插入老师
insert into teacher values('张三')
insert into teacher values('李四')
insert into teacher values('王五')
insert into teacher values('赵六')
--插入课程的名字
insert into Subject values(1,'SqlServer',1)
insert into Subject values(2,'c#',2)
insert into Subject values(3,'asp.net',3)
insert into Subject values(4,'java',4)
--插入学生的名字
insert into Student values('小剑')
insert into Student values('小静')
insert into Student values('小湖')
insert into Student values('小周')
--插入学号为1的学生的各门成绩
insert into Achievement values(1,2,66)
insert into Achievement values(1,1,60)
insert into Achievement values(1,3,89)
insert into Achievement values(1,4,87)
--插入学号为2的学生的各门成绩
insert into Achievement values(2,2,77)
insert into Achievement values(2,1,80)
insert into Achievement values(2,3,84)
insert into Achievement values(2,4,53)
--查询
--模糊查询
select sid from student where sname like '%小%'
--查询小剑的各门成绩及相应的任课老师
--及显示
--学生姓名 课程名称 分数 任课老师
select student.sname as 学生姓名,subject.sjname 课程名称,Achievement.ascore 分数,teacher.tname 任课老师 from Achievement
inner join student on Achievement.asid=student.sid inner join subject on Achievement.asjid=subject.sjid
inner join teacher on teacher.tid=Subject.sjid
--求出对应课程的老师
select subject.sjname,teacher.tname from teacher inner join subject on teacher.tid=subject.sjtid
--三表连接 查出学生的姓名,课程及相应的成绩
select student.sname,subject.sjname,Achievement.ascore from Achievement
inner join student on Achievement.asid=student.sid inner join subject on Achievement.asjid=subject.sjid
--求所有的分数
select sum(ascore) from Achievement
--求班上SqlServer的平均成绩
select avg(ascore) from Achievement (select sjid from subject where sjname like '%Sqlserver%')
--求小剑的总分
select sum(ascore) from Achievement where asid=(select sid from student where sname='小剑')
--求小剑的平均分
select avg(ascore) from Achievement where asid=(select sid from student where sname='小剑')
--求小剑的最底分
select min(ascore) from Achievement where asid=(select sid from student where sname='小剑')
--求小剑的最高分
select max(ascore) from Achievement where asid=(select sid from student where sname='小剑')
--根据不同的课程求总成绩 (分组)
select sum(ascore) from achievement group by asjid
--根据成绩降序排
select * from Achievement order by ascore desc
普通行列转换
--
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name as 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
-------------------------------------------------------------------
加个平均分,总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
drop table tb
---------------------------------------------------------
如果上述两表互相换一下:即
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
--------------------------------------------------------------------
加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb1
- 行列转行
- 列转行-行列转换
- 关于 行列相互转行
- 行列转换(列转行)
- SQL 列转行问题。普通行列转换。
- 一列多行进行列转行操作
- sql 行专列 列转行 普通行列转换
- sql 行专列 列转行 普通行列转换
- sql 行专列 列转行 普通行列转换
- oracle中的行列转换(一 列转行 pivot)
- SQL中PIVOT 行列转换;UNPIVOT列转行
- 转行
- 转行
- 转行
- 转行
- SQL Server 2005之PIVOT/UNPIVOT行列转换(行转列、列转行)
- 行列
- 转行 转行 转行
- 2011年3月8日 晴
- 【转】多线程
- 心灵密探
- 古老的经典笔试题-线程
- 2011年度微博开放平台第三方应用布局预测及Twitter代表应用分析
- 行列转行
- P->NP->NP-complete-NP-hard问题之浅析
- 如何搭建Repo服务器端
- 听雨而眠
- 对于Linux下fork调用的理解
- 麦肯锡公司
- 一步一步地实现菜单栏(JMenuBar)工具栏(JPopupMenu)组合框(JComboBox)复选框(JCheckBox)单选按钮(JRadioButton)文本域的综合应用(一)
- Windows7部署Android开发环境傻瓜式教程(Eclipse+ADT) (转)
- android进度条