天天看點
傳回

SQL中進行轉列的幾種方式

SQL中進行專列

  • ​​SQL中進行轉列​​
  • ​​以下是這次sql轉換的表結構以及資料​​
  • ​​資料準備​​
  • ​​1、學生表​​
  • ​​2、課程表​​
  • ​​3、成績表​​
  • ​​4、基本資料​​
  • ​​我們先看一下最基本的查詢效果是什麼樣的​​
  • ​​靜态行轉列​​
  • ​​動态行轉列​​
  • ​​動态的列是拿到了，那如何再結合SQL語句進行查詢得到結果呢？​​
  • ​​存儲過程--動态行轉列​​

SQL中進行轉列

在很多筆試的程式員中會有很多寫SQL的情況，其中很多時候會考察行轉列。那麼這個時候如果能寫出來幾種行轉列的SQL，會給面試官留下比較好的印象。

以下是這次sql轉換的表結構以及資料

SQL中進行轉列的幾種方式
SQL中進行轉列的幾種方式
SQL中進行轉列的幾種方式

資料準備

1、學生表 

CREATE TABLE `student` (
   `stuid` VARCHAR(16) NOT NULL COMMENT '學号',
   `stunm` VARCHAR(20) NOT NULL COMMENT '學生姓名',
   PRIMARY KEY (`stuid`)
  )
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB;

2、課程表 

CREATE TABLE `curriculum` (
   `courseno` VARCHAR(20) NOT NULL,
   `coursenm` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`courseno`)
  )
  COMMENT='課程表'
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB;

3、成績表 

CREATE TABLE `score` (
   `stuid` VARCHAR(16) NOT NULL,
   `courseno` VARCHAR(20) NOT NULL,
   `scores` FLOAT NULL DEFAULT NULL,
   PRIMARY KEY (`stuid`, `courseno`)
  )
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB;

4、基本資料 

/*學生表資料*/
  Insert Into student (stuid, stunm) Values('1001', '張三');
  Insert Into student (stuid, stunm) Values('1002', '李四');
  Insert Into student (stuid, stunm) Values('1003', '趙二');
  Insert Into student (stuid, stunm) Values('1004', '王五');
  Insert Into student (stuid, stunm) Values('1005', '劉青');
  Insert Into student (stuid, stunm) Values('1006', '周明');
  /*課程表資料*/
  Insert Into curriculum (courseno, coursenm) Values('C001', '大學國文');
  Insert Into curriculum (courseno, coursenm) Values('C002', '新視野英語');
  Insert Into curriculum (courseno, coursenm) Values('C003', '離散數學');
  Insert Into curriculum (courseno, coursenm) Values('C004', '機率論與數理統計');
  Insert Into curriculum (courseno, coursenm) Values('C005', '線性代數');
  Insert Into curriculum (courseno, coursenm) Values('C006', '高等數學(一)');
  Insert Into curriculum (courseno, coursenm) Values('C007', '高等數學(二)');
  /*成績表資料*/
  Insert Into number_result(stuid, courseno, scores) Values('1001', 'C001', 67);
  Insert Into number_result(stuid, courseno, scores) Values('1002', 'C001', 68);
  Insert Into number_result(stuid, courseno, scores) Values('1003', 'C001', 69);
  Insert Into number_result(stuid, courseno, scores) Values('1004', 'C001', 70);
  Insert Into number_result(stuid, courseno, scores) Values('1005', 'C001', 71);
  Insert Into number_result(stuid, courseno, scores) Values('1006', 'C001', 72);
  Insert Into number_result(stuid, courseno, scores) Values('1001', 'C002', 87);
  Insert Into number_result(stuid, courseno, scores) Values('1002', 'C002', 88);
  Insert Into number_result(stuid, courseno, scores) Values('1003', 'C002', 89);
  Insert Into number_result(stuid, courseno, scores) Values('1004', 'C002', 90);
  Insert Into number_result(stuid, courseno, scores) Values('1005', 'C002', 91);
  Insert Into number_result(stuid, courseno, scores) Values('1006', 'C002', 92);
  Insert Into number_result(stuid, courseno, scores) Values('1001', 'C003', 83);
  Insert Into number_result(stuid, courseno, scores) Values('1002', 'C003', 84);
  Insert Into number_result(stuid, courseno, scores) Values('1003', 'C003', 85);
  Insert Into number_result(stuid, courseno, scores) Values('1004', 'C003', 86);
  Insert Into number_result(stuid, courseno, scores) Values('1005', 'C003', 87);
  Insert Into number_result(stuid, courseno, scores) Values('1006', 'C003', 88);
  Insert Into number_result(stuid, courseno, scores) Values('1001', 'C004', 88);
  Insert Into number_result(stuid, courseno, scores) Values('1002', 'C004', 89);
  Insert Into number_result(stuid, courseno, scores) Values('1003', 'C004', 90);
  Insert Into number_result(stuid, courseno, scores) Values('1004', 'C004', 91);
  Insert Into number_result(stuid, courseno, scores) Values('1005', 'C004', 92);
  Insert Into number_result(stuid, courseno, scores) Values('1006', 'C004', 93);
  Insert Into number_result(stuid, courseno, scores) Values('1001', 'C005', 77);
  Insert Into number_result(stuid, courseno, scores) Values('1002', 'C005', 78);
  Insert Into number_result(stuid, courseno, scores) Values('1003', 'C005', 79);
  Insert Into number_result(stuid, courseno, scores) Values('1004', 'C005', 80);
  Insert Into number_result(stuid, courseno, scores) Values('1005', 'C005', 81);
  Insert Into number_result(stuid, courseno, scores) Values('1006', 'C005', 82);
  Insert Into number_result(stuid, courseno, scores) Values('1001', 'C006', 77);
  Insert Into number_result(stuid, courseno, scores) Values('1002', 'C006', 78);
  Insert Into number_result(stuid, courseno, scores) Values('1003', 'C006', 79);
  Insert Into number_result(stuid, courseno, scores) Values('1004', 'C006', 80);
  Insert Into number_result(stuid, courseno, scores) Values('1005', 'C006', 81);
  Insert Into number_result(stuid, courseno, scores) Values('1006', 'C006', 82);

我們先看一下最基本的查詢效果是什麼樣的

SQL中進行轉列的幾種方式

靜态行轉列 

Select st.stuid, st.stunm, 
   MAX(CASE c.coursenm WHEN '大學國文' THEN s.scores ELSE 0 END ) '大學國文',
   MAX(CASE c.coursenm WHEN '新視野英語' THEN ifnull(s.scores,0) ELSE 0 END ) '新視野英語', 
   MAX(CASE c.coursenm WHEN '離散數學' THEN ifnull(s.scores,0) ELSE 0 END ) '離散數學',
   MAX(CASE c.coursenm WHEN '機率論與數理統計' THEN ifnull(s.scores,0) ELSE 0 END ) '機率論與數理統計',
   MAX(CASE c.coursenm WHEN '線性代數' THEN ifnull(s.scores,0) ELSE 0 END ) '線性代數',
   MAX(CASE c.coursenm WHEN '高等數學(一)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等數學(一)',
   MAX(CASE c.coursenm WHEN '高等數學(二)' THEN ifnull(s.scores,0) ELSE 0 END ) '高等數學(二)'
  From student st
  Left Join number_result s On st.stuid = s.stuid
  Left Join curriculum c On c.courseno = s.courseno
  Group by st.stuid
SQL中進行轉列的幾種方式

很多人肯定不了解為什麼要使用Max函數，實際上大家都知道聚合函數是和分組進行搭配使用的。這一點毋庸置疑，那麼大家可以把Max函數去掉看看會顯示什麼效果。切記去掉Max函數記得把分組也去掉，這樣才能看到本質。

SQL中進行轉列的幾種方式

這時大家會發現沒列都出現了重複的資料，而且隻有一列是有值得。其他列都是0.那麼這個時候就應該能很清楚的認識到，為什麼使用Max函數了。在分組的同時取一組中的最大值。

靜态行轉列有一個弊端就是第一确定有多少個課程，然後再把課程名稱拿出來再寫查詢語句。但是這樣會寫很多東西。

動态行轉列

首先我們要動态的擷取是列的資料 ：

MAX(CASE c.coursenm WHEN ‘大學國文’ THEN s.scores ELSE 0 END ) ‘大學國文’,

MAX(CASE c.coursenm WHEN ‘線性代數’ THEN ifnull(s.scores,0) ELSE 0 END ) ‘線性代數’,

MAX(CASE c.coursenm WHEN ‘離散數學’ THEN ifnull(s.scores,0) ELSE 0 END ) ‘離散數學’

這裡想動态的擷取到上面的就需要拼接sql列 ：

SELECT

GROUP_CONCAT( DISTINCT CONCAT( ‘MAX(IF(c.coursenm = ‘’’, c.coursenm, ‘’’, s.scores, 0)) AS ‘’’, c.coursenm, ‘’’’ ) )

FROM

curriculum c;

SQL中進行轉列的幾種方式

在這裡解釋一下 ：

concat()函數 ： 将多個字元串連接配接成一個字元串。

文法：concat_ws(separator, str1, str2, …)

說明：第一個參數指定分隔符。需要注意的是分隔符不能為null，如果為null，則傳回結果為null。

group_concat()函數 ：将group by産生的同一個分組中的值連接配接起來，傳回一個字元串結果。

文法：group_concat( [distinct] 要連接配接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )。

說明：通過使用distinct可以排除重複值；如果希望對結果中的值進行排序，可以使用order by子句；separator是一個字元串值，預設為一個逗号。

動态的列是拿到了，那如何再結合SQL語句進行查詢得到結果呢？ 

這裡要說明一點，因為用到了拼接函數，如果像上面的查詢語句，隻是把那幾行語句替換掉，也就是下面這樣  
Select st.stuid, st.stunm, 
(
 SELECT
 GROUP_CONCAT(DISTINCT
 CONCAT(
 'MAX(IF(c.coursenm = ''',
 c.coursenm,
 ''', s.scores, NULL)) AS ',
 c.coursenm
 )
 )
 FROM curriculum c
)
From Student st
Left Join number_result s On st.stuid = s.stuid
Left Join curriculum c On c.courseno = s.courseno
Group by st.stuid;
SQL中進行轉列的幾種方式

最終結果如下 ：

SET @SQL = NULL;

SELECT

GROUP_CONCAT( DISTINCT CONCAT( ‘MAX(IF(c.coursenm = ‘’’, c.coursenm, ‘’’, s.scores, 0)) AS ‘’’, c.coursenm, ‘’’’ ) ) INTO @SQL

FROM

curriculum c;

SET @SQL = CONCAT( 'Select st.stuid, st.stunm, ‘, @SQL, ’ From Student st

Left Join number_result s On st.stuid = s.stuid

Left Join curriculum c On c.courseno = s.courseno

Group by st.stuid’ );

PREPARE stmt

FROM

@SQL;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SQL中進行轉列的幾種方式

存儲過程–動态行轉列 

用存儲過程的好處是，友善我們調用，相當于一個函數，其他可能也是類似的查詢不需再重複寫代碼，直接調存儲過程就好，還能随心所欲的加上if條件判斷。建立存儲過程的語句我就不多寫了，這裡把上面的查詢語句直接放到建立存儲過程的begin和end直接就可以了，如下：
DELIMITER && 
drop procedure if exists SP_QueryData;
Create Procedure SP_QueryData(IN stuid varchar(16))
READS SQL DATA 
BEGIN
 
SET @sql = NULL;
SET @stuid = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
 CONCAT(
 'MAX(IF(c.coursenm = ''',
 c.coursenm,
 ''', s.scores, 0)) AS ''',
 c.coursenm, '''
 )
 ) INTO @sql
FROM curriculum c;
 
SET @sql = CONCAT('Select st.stuid, st.stunm, ', @sql, 
 ' From student st 
 Left Join number_result s On st.stuid = s.stuid
 Left Join curriculum c On c.courseno = s.courseno');
 
IF stuid is not null and stuid <> '' then
SET @stuid = stuid;
SET @sql = CONCAT(@sql, ' Where st.stuid = '', @stuid, ''');
END IF; 
 
SET @sql = CONCAT(@sql, ' Group by st.stuid');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
END && 
DELIMITER ;
sql 優化 行轉列 資料
上一篇: 用MyEclipse 無法儲存
下一篇: 需求分析師怎麼煉成的

繼續閱讀