表格的横纵转

以下代码由PHP站长网 52php.cn网络自互联网

此刻PHP站长网小编把它分享给各人，仅供参考

##############行转列-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 StudentScores
GROUP 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 '王五','工商银行卡',IsSuccess) SELECT '赵六','建树银行卡',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 '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡',CASE PayType WHEN '建树银行卡' THEN SUM(Money) ELSE 0 END AS '建树银行卡'       
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

以上内容由PHP站长网【52php.cn】网络清算供各人参考研究

假如以上内容对您有辅佐，接待保藏、点赞、保举、分享。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!