>

SQL语句行列调换方法有怎么着,when与pivot函数的

- 编辑:www.bifa688.com -

SQL语句行列调换方法有怎么着,when与pivot函数的

  SQL语句行列转变的三种方法 case...when和pivot函数应用,运用pivot 函数只辅助数据库版本二〇〇七上述的。一般采用case when else end 的不二等秘书诀非常多,相比较常见

/*创造数据库*/
 CREATE DATABASE tmp
 go
 USE tmp
 go

/*始建数据库*/
 CREATE DATABASE tmp
 go
 USE tmp
 go

  /*开创数据库*/

/*创制数据库测量试验表*/
CREATE TABLE [Scores]
     (
       [ID] INT IDENTITY(1, 1)
                PRIMARY KEY ,
       [Student] VARCHAR(20) ,
       [Subject] VARCHAR(30) ,
       [Score] FLOAT
     )

/*创建数据库测量检验表*/
CREATE TABLE [Scores]
     (
       [ID] INT IDENTITY(1, 1)
                PRIMARY KEY ,
       [Student] VARCHAR(20) ,
       [Subject] VARCHAR(30) ,
       [Score] FLOAT
     )

  CREATE DATABASE tmp

go

go

  go

TRUNCATE TABLE Scores
 /*布置数据库测量检验数据消息*/
  INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test001', '语文', '90' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test001', '英语', '85' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'text002', '语文', '90' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'text002', '英语', '80' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test003', '语文', '95' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test003', '英语', '85' )

TRUNCATE TABLE Scores
 /*计划数据库测量检验数据音信*/
  INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test001', '语文', '90' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test001', '英语', '85' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'text002', '语文', '90' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'text002', '英语', '80' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test003', '语文', '95' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test003', '英语', '85' )

  USE tmp

/*1.  case when .......then else  ....end 用法,行列调换*/
 SELECT  Student AS '姓名' ,
         MAX(CASE Subject
               WHEN '语文' THEN Score
               ELSE 0
             END) AS '语文' ,--要是那么些行是“语文”,就选此行作为列
        MAX(CASE Subject
               WHEN '英语' THEN Score
               ELSE 0
             END) AS '英语'
FROM    Scores
 GROUP BY Student
 ORDER BY Student

/*1.  case when .......then else  ....end 用法,行列转变*/
 SELECT  Student AS '姓名' ,
         MAX(CASE Subject
               WHEN '语文' THEN Score
               ELSE 0
             END) AS '语文' ,--假若这么些行是“语文”,就选此行作为列
        MAX(CASE Subject
               WHEN '英语' THEN Score
               ELSE 0
             END) AS '英语'
FROM    Scores
 GROUP BY Student
 ORDER BY Student

  go

/*2. pivot(聚合函数(要转成列值的列名)
       for 要转移的列
       in(指标列名)
   )*/

/*2. pivot(聚合函数(要转成列值的列名)
       for 要改造的列
       in(指标列名)
   )*/

  /*创设数据库测量试验表*/

SELECT  Student AS '姓名' ,
         AVG(语文) AS '语文' ,
         AVG(英语) AS '英语'
FROM    Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores
 GROUP BY Student
 ORDER BY Student ASC

SELECT  Student AS '姓名' ,
         AVG(语文) AS '语文' ,
         AVG(英语) AS '英语'
FROM    Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores
 GROUP BY Student
 ORDER BY Student ASC

  CREATE TABLE [Scores]

你或然感兴趣的小说:

  • MySQL 的CASE WHEN 语句使用表达
  • MySQL的CASE WHEN语句的多少个利用实例
  • MySQL中使用case when 语句完结多规格查询的办法
  • SQL会集函数中case when then 使用手艺
  • sql学习之CASE WHEN THEN ELSE END的用法
  • sql 中 case when 语法使用办法
  • sum(case when then)(剖断孩子生的个数)
  • 至于case when语句的报错难点详解

CREATE DATABASE tmp go USE tmp go /*创设数据库测量试验表*/ CREATE TABLE [Scores] ( [ID] INT IDENTITY(1, 1) PRIMARY KEY , [Student] VARCHAR(20) , [Subject] V...

  (

  [ID] INT IDENTITY(1, 1)

  PRIMARY KEY ,

  [Student] VARCHAR(20) ,

  [Subject] VARCHAR(30) ,

  [Score] FLOAT

  )

  go

  TRUNCATE TABLE Scores

  /*插入数据库测验数据新闻*/

  INSERT INTO Scores

  ( Student, Subject, Score )

  VALUES ( 'test001', '语文', '90' )

  INSERT INTO Scores

  ( Student, Subject, Score )

  VALUES ( 'test001', '英语', '85' )

  INSERT INTO Scores

  ( Student, Subject, Score )

  VALUES ( 'text002', '语文', '90' )

  INSERT INTO Scores

  ( Student, Subject, Score )

  VALUES ( 'text002', '英语', '80' )

  INSERT INTO Scores

  ( Student, Subject, Score )

  VALUES ( 'test003', '语文', '95' )

  INSERT INTO Scores

  ( Student, Subject, Score )

  VALUES ( 'test003', '英语', '85' )

  /*1. case when .......then else ....end 用法,行列转变*/

  SELECT Student AS '姓名' ,

  MAX(CASE Subject

  WHEN '语文' THEN Score

  ELSE 0

  END) AS '语文' ,--假设那几个行是“语文”,就选此行作为列

  MAX(CASE Subject

  WHEN '英语' THEN Score

  ELSE 0

  END) AS '英语'

  FROM Scores

  GROUP BY Student

  ORDER BY Student

  /*2. pivot(聚合函数(要转成列值的列名)

  for 要调换的列

  in(指标列名)

  )*/

  SELECT Student AS '姓名' ,

  AVG(语文) AS '语文' ,

  AVG(英语) AS '英语'

  FROM Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores

  GROUP BY Student

  ORDER BY Student ASC

 

case...when和pivot函数应用,运用pivot 函数只支持数据库版本二〇〇六之上的。一般选用case when else end 的方式非常多,比...

本文由88bifa必发唯一官网发布,转载请注明来源:SQL语句行列调换方法有怎么着,when与pivot函数的