表格的横纵转

概述表格的横纵转

下面是内存溢出 jb51.cc 通过网络收集整理的代码片段。

内存溢出小编现在分享给大家，也给大家做个参考。

##############行转列-example 1,学生成绩###############创建数据库表CREATE  table Studentscores  (      Username         NVARCHAR(20),Subject          NVARCHAR(30),score            float                      )  ;      #插入模拟数据INSERT INTO Studentscores SELECT 'Nick','语文',80 ; INSERT INTO Studentscores SELECT 'Nick','数学',90 ;INSERT INTO Studentscores SELECT 'Nick','英语',70 ; INSERT INTO Studentscores SELECT 'Nick','生物',85 ;INSERT INTO Studentscores SELECT 'Kent',80 ;INSERT INTO Studentscores SELECT 'Kent',90 ;INSERT INTO Studentscores SELECT 'Kent',70 ;INSERT INTO Studentscores SELECT 'Kent',85 ;#查询每个学生的各科成绩SELECT Username,MAX(CASE Subject WHEN '语文' THEN score ELSE 0 END) AS '语文',MAX(CASE Subject WHEN '数学' THEN score ELSE 0 END) AS '数学',MAX(CASE Subject WHEN '英语' THEN score ELSE 0 END) AS '英语',MAX(CASE Subject WHEN '生物' THEN score ELSE 0 END) AS '生物' FROM StudentscoresGROUP BY Username ;##############行转列-example 2,游戏玩家充值###############创建数据库表CREATE table Inpours  (       ID             INT auto_INCREMENT,Username          NVARCHAR(20),CreateTime     DATETIME,PayType         NVARCHAR(20),Money             DECIMAL,IsSuccess         BIT,CONSTRAINT PK_Inpours_ID PRIMARY KEY(ID)  )   ;#插入模拟数据INSERT INTO Inpours(Username,CreateTime,PayType,Money,IsSuccess) SELECT '张三','2010-05-01','支付宝',50,1  ;INSERT INTO Inpours(Username,'2010-06-14','手机短信',100,IsSuccess) SELECT '李四',1  ;  INSERT INTO Inpours(Username,'2010-07-14',IsSuccess) SELECT '王五','工商yhk',IsSuccess) SELECT '赵六','建设yhk',1  ;#要求按日期、支付方式来统计充值金额信息select CreateTime,CASE PayType WHEN '支付宝' THEN SUM(Money) ELSE 0 END AS '支付宝',CASE PayType WHEN '手机短信' THEN SUM(Money) ELSE 0 END AS '手机短信',CASE PayType WHEN '工商yhk' THEN SUM(Money) ELSE 0 END AS '工商yhk',CASE PayType WHEN '建设yhk' THEN SUM(Money) ELSE 0 END AS '建设yhk'       FROM Inpours GROUP BY CreateTime,PayType##############列转行-example 1,供货信息###############创建数据库表CREATE table ProgrectDetail  (      Progrectname         NVARCHAR(20),OverseaSupply        INT,NativeSupply         INT,SouthSupply          INT,northSupply          INT             )#插入模拟数据   INSERT INTO ProgrectDetail    SELECT 'A',200,50     UNION ALL SELECT 'B',300,150,150     UNION ALL SELECT 'C',159,400,20,320     UNION ALL SELECT 'D',250,30,15,15#SELECT Progrectname,'OverseaSupply' AS supplier,MAX(OverseaSupply) AS 'SupplyNum' FROM ProgrectDetail  GROUP BY Progrectname  UNION ALL SELECT Progrectname,'NativeSupply' AS supplier,MAX(NativeSupply) AS 'SupplyNum' FROM ProgrectDetail  GROUP BY Progrectname  UNION ALL SELECT Progrectname,'SouthSupply' AS supplier,MAX(SouthSupply) AS 'SupplyNum' FROM ProgrectDetail  GROUP BY Progrectname UNION ALL SELECT Progrectname,'northSupply' AS supplier,MAX(northSupply) AS 'SupplyNum' FROM ProgrectDetail  GROUP BY Progrectname@H_419_5@																		
以上是内存溢出(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。
 如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
          总结       
以上是内存溢出为你收集整理的表格的横纵转全部内容，希望文章能够帮你解决表格的横纵转所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/sjk/1165209.html