资源描述：
《关于sql行转列的探讨,由一道试题引出》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、Sql综合查询与行转列示例一道试题,查询结果为最后一个表.如图：示例代码一：createtableEducationLevel(LevelIdintprimarykey,LevelNamevarchar(50),Gradesint)insertintoEducationLevelvalues(1,'博士',50)insertintoEducationLevelvalues(2,'硕士',40)insertintoEducationLevelvalues(3,'本科',30)insertintoEducationLevelvalues(4,'专科',20)insertintoEducatio
2、nLevelvalues(5,'专科以下',10)createtableDepartment(DepartmentIdintprimarykey,DepartmentNamevarchar(50))insertintoDepartmentvalues(1,'历史系')insertintoDepartmentvalues(2,'经济学院')insertintoDepartmentvalues(3,'管理学院')insertintoDepartmentvalues(4,'外语系')createtablePersonnel(PersonIdintprimarykey,PersonNamevarc
3、har(50),EducationLevelint,Departmentint)insertintoPersonnelvalues(1,'刘雪飞',3,1)insertintoPersonnelvalues(2,'张红霞',3,1)insertintoPersonnelvalues(3,'王刚',1,2)insertintoPersonnelvalues(4,'李良',4,2)insertintoPersonnelvalues(5,'肖楠',1,4)insertintoPersonnelvalues(6,'于涛',3,4)insertintoPersonnelvalues(7,'孙小雪',
4、4,3)insertintoPersonnelvalues(8,'高溪',3,3)insertintoPersonnelvalues(9,'赵柯',4,3)insertintoPersonnelvalues(10,'刘鹏飞',2,4)selectdepartment,departmentnameas'院系',sum(casewhenlevelname='博士'then1else0end)as'博士人数',max(casewhenlevelname='博士'thengradeselse0end)as'博士分数',sum(casewhenlevelname='硕士'then1else0end)
5、as'硕士人数',max(casewhenlevelname='硕士'thengradeselse0end)as'硕士分数',sum(casewhenlevelname='本科'then1else0end)as'本科人数',max(casewhenlevelname='本科'thengradeselse0end)as'本科分数',sum(casewhenlevelname='专科'then1else0end)as'专科人数',max(casewhenlevelname='专科'thengradeselse0end)as'专科分数',sum(casewhenlevelname='专科以下't
6、hen1else0end)as'专科以下人数',max(casewhenlevelname='专科以下'thengradeselse0end)as'专科以下分数',sum(grades)as'汇总'from(selecta.*,b.levelname,b.grades,c.departmentnamefrompersonnelainnerjoineducationlevelbona.educationlevel=b.levelidinnerjoindepartmentcona.department=c.departmentid)tgroupbydepartment,departmentna
7、meorderbydepartment--或者sql语句selecta.department,c.departmentnameas'院系',sum(casewhenb.levelname='博士'then1else0end)as'博士人数',max(casewhenb.levelname='博士'thenb.gradeselse0end)as'博士分数',sum(casewhenb.levelname='硕士'then1
