数据库表行列转换

1.create table student  
2.(  
3.    s_name varchar(20),  
4.    s_subject varchar(20),  
5.    s_point int 
6.)  
7. 
8.insert into student values('张三','数学',86)  
9.insert into student values('李四','数学',78)  
10.insert into student values('张三','语文',67)  
11.insert into student values('张三','英语',90)  
12.insert into student values('李四','英语',88)  
13.insert into student values('李四','语文',70)  
14.insert into student values('王五','英语',90)  
15.insert into student values('王五','数学',88)  
16.insert into student values('王五','语文',70)  
17.select * from student  

[]1[/align]



1.select s_name as '姓名',s_point as '数学分数' from student where s_subject='数学' order by s_point desc  
2.--如下图：

[img][/img]

1.select s_name as '姓名',avg(s_point) as '平均成绩' from student group by s_name  
2.--如下图： 
[img][/img]


1.select n.s_name as '姓名',n.shuxue as '数学',n.yuwen as '语文',n.yingyu as '英语',n.pingjun as '平均成绩',n.allcount as '总分' from  
2.(  
3.    select s_name as s_name,  
4.    (select s_point from student where s.s_name=s_name and s_subject='数学') as shuxue,  
5.    (select s_point from student where s.s_name=s_name and s_subject='语文') as yuwen,  
6.    (select s_point from student where s.s_name=s_name and s_subject='英语') as yingyu,  
7.    avg(s_point) as pingjun,  
8.    sum(s_point) as allcount  
9.    from student s group by s_name   
10.      
11.)n order by allcount desc

评论

2 楼 高级java工程师 2011-03-25  

嗯，是的其实就是转换

1 楼 JIRA 2011-03-18  

装换，貌似是"转换"？