关于数据库一些行列转置的问题

关于数据库笔试中遇到的一些行列转置的问题

 

最近遇到的一题数据库行列转置的题目，然后网上查资料，整理出这篇博客，希望以后遇到类似和同样的题目能轻松做出来。

 

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 ;