萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> SQL語句行列轉換方法有哪些

SQL語句行列轉換方法有哪些

  SQL語句行列轉換的兩種方法 case...when和pivot函數應用,運用pivot 函數只支持數據庫版本2005以上的。一般運用case when else end 的方法比較多,比較普遍

  /*創建數據庫*/

  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

  )

  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

 

copyright © 萬盛學電腦網 all rights reserved