关于数据库笔试中遇到的一些行列转置的问题
最近遇到的一题数据库行列转置的题目，然后网上查资料，整理出这篇博客，希望以后遇到类似和同样的题目能轻松做出来。
1、问题：假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
想变成(得到如下结果)：
姓名语文数学物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
create table tb(姓名varchar(10) ,
课程 varchar(10) , 分数 int);
insert into tb values('张三' , '语文' , 74);
insert into tb values('张三' , '数学' , 83);
insert into tb values('张三' , '物理' , 93);
insert into tb values('李四' , '语文' , 74);
insert into tb values('李四' , '数学' , 84);
insert into tb values('李四' , '物理' , 94);
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以
下同)
select 姓名 as
姓名 ,
max(case 课程 when '语文' then
分数 else 0 end) 语文,
max(case 课程 when '数学' then
分数 else 0 end) 数学,
max(case 课程 when '物理' then
分数 else 0 end) 物理
from tb
group by 姓名
select 姓名 as
姓名,
max(case when 课程='语文' then
分数 else 0end) 语文,
max(case when 课程='数学' then
分数 else 0end) 数学,
max(case when 课程='物理' then
分数 else 0end) 物理
from tb group by 姓名;
2、问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
select 姓名as
姓名,
max(case when
课程='语文'then
分数 else 0 end)
语文,
max(case when
课程='数学'then
分数 else 0 end)
数学,
max(case when
课程='物理'then
分数 else 0 end)
物理,
cast(avg(分数*1.0)as decimal(18,2))
平均分,
sum(分数)总分
from tb group by
姓名;
3、问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三 748393
李四 748494
想变成(得到如下结果)：
姓名课程分数
---- ---- ----
李四语文 74
李四数学 84
李四物理 94
张三语文 74
张三数学 83
张三物理 93
--------------
create table tb(姓名varchar(10) ,
语文 int ,
数学int , 物理 int);
insert into tb values('张三',74,83,93);
insert into tb values('李四',74,84,94);
select * from
(
select
姓名 , 课程= '语文' ,
分数 =
语文from tb
union all
select
姓名 , 课程= '数学' ,
分数 =
数学from tb
union all
select
姓名 , 课程= '物理' ,
分数 =
物理from tb
) t
order by 姓名, case
课程 when '语文' then 1 when '数学'then 2 when '物理'
then 3 end
这代码运行不了，报错，不知道是因为使用mysql才会出错还是什么原因，知道的同学请告知一下，感激不尽。错误提示：“ERROR 1054 (42S22): Unknown column '课程' in 'field list'”
4、问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名课程分数
---- ------ ------
李四语文 74.00
李四数学 84.00
李四物理 94.00
李四平均分 84.00
李四总分 252.00
张三语文 74.00
张三数学 83.00
张三物理 93.00
张三平均分 83.33
张三总分 250.00
------------------
select * from
(
select 姓名 as
姓名 ,
课程= '语文' ,
分数=
语文 from tb
union all
select 姓名 as
姓名 ,
课程= '数学' ,
分数 =
数学from tb
union all
select 姓名 as
姓名 ,
课程= '物理' ,
分数 =
物理from tb
union all
select 姓名 as
姓名 ,
课程= '平均分' ,
分数 = cast((语文+
数学 +
物理)*1.0/3 as decimal(18,2)) fromtb
union all
select 姓名as
姓名 ,
课程= '总分' ,
分数 =
语文+ 数学 +
物理 from tb
) t
order by 姓名 , case
课程when '语文' then 1 when '数学' then 2 when '物理'then
3 when '平均分' then 4 when '总分'then 5 end
同样代码运行不了，错误提示和上面一样，不知道怎么解决。
5、一道SQL语句面试题，关于group
by
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果,
该如何写sql语句?
胜负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table test(rq varchar(10),shengfunchar(1));
insert into test values ('2005-05-09','胜');
insert into test values ('2005-05-09','胜');
insert into test values ('2005-05-09','负');
insert into test values ('2005-05-09','负');
insert into test values ('2005-05-10','胜');
insert into test values ('2005-05-10','负');
insert into test values ('2005-05-10','负');
select rq ,
sum(case when shengfu='胜' then 1else 0 end)
胜,
sum(case when shengfu='负' then 1else 0 end)
负
from test group by rq;
ORACLE
两种简单的行列转置
--------------------------------------------------------------------------------
1、固定列数的行列转换
如
student subject grade
--------- ---------- --------
student1 语文80
student1 数学70
student1 英语60
student2 语文90
student2 数学80
student2 英语100
……
转换为
语文数学英语
student1 80 70 60
student2 90 80 100
……
语句如下：
selectstudent,
sum (decode(subject, ' 语文' , grade, null )) "语文",
sum (decode(subject, ' 数学' , grade, null )) "数学",
sum (decode(subject, ' 英语' , grade, null )) "英语"
from table
group by student;
2、不定列行列转换
如
c1 c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATEOR REPLACE FUNCTION get_c2(tmp_c1 NUMBER )
RETURN VARCHAR2
IS
Col_c2 VARCHAR2 ( 4000 );
BEGIN
FOR cur IN ( SELECT c2 FROM t WHERE c1 = tmp_c1) LOOP
Col_c2 : = Col_c2 || cur.c2;
END LOOP;
Col_c2 : = rtrim (Col_c2, 1 );
RETURN Col_c2;
END ;
/
SQL > select distinct c1 ,get_c2(c1) cc2 from table ;