表格的横纵转

来源：互联网发布：php beast 解密编辑：程序博客网时间：2024/05/23 00:05

###################################行转列-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 , CreateTime , PayType , Money , IsSuccess) SELECT '张三', '2010-06-14', '支付宝', 50, 1 ;
INSERT INTO Inpours(UserName , CreateTime , PayType , Money , IsSuccess) SELECT '张三', '2010-06-14', '手机短信', 100, 1 ;
INSERT INTO Inpours(UserName , CreateTime , PayType , Money , IsSuccess) SELECT '李四', '2010-06-14', '手机短信', 100, 1 ;
INSERT INTO Inpours(UserName , CreateTime , PayType , Money , IsSuccess) SELECT '李四', '2010-07-14', '支付宝', 100, 1 ;
INSERT INTO Inpours(UserName , CreateTime , PayType , Money , IsSuccess) SELECT '王五', '2010-07-14', '工商银行卡', 100, 1 ;
INSERT INTO Inpours(UserName , CreateTime , PayType , Money , IsSuccess) SELECT '赵六', '2010-07-14', '建设银行卡', 100, 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', 100, 200, 50, 50
   UNION ALL SELECT 'B', 200, 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