数据库行列转换

源：http://wenku.baidu.com/view/5dda306127d3240c8447efb1.html

评：

<!--[if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->

要求查询出的数据：

mysql> create table student (id varchar(50), name varchar(50), subject varchar(5
0), result int, primary key (id));

 

 

插入数据：
insert into student values(‘1’,'zhangsan','yuwen',80)
insert into student values(‘2’,'zhangsan','shuxue',70)
insert into student values(‘3’,'zhangsan','wuli',90)
insert into student values(‘4’,'lisi','yuwen',75)
insert into student values(‘5’,'lisi','shuxue',84)
insert into student values(‘6’,'lisi','wuli',69)

 

<!--[if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->

**************   查询语句   **************

 

select name, max(case subject when 'shuxue' then  else 0 end) shuxue, max(case subject when 'yuwen' then  else 0 end) yuwen, max(case subject when 'wuli' then  else 0 end) wuli from student group by name;