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