SQL中進行轉列的幾種方式

来源:https://www.cnblogs.com/haizai/archive/2019/05/04/10809587.html
-Advertisement-
Play Games

SQL中進行轉列 在很多筆試的程式員中會有很多寫SQL的情況，其中很多時候會考察行轉列。那麼這個時候如果能寫出來幾種行轉列的SQL，會給面試官留下比較好的印象。 以下是這次sql轉換的表結構以及數據 數據準備 1、學生表 CREATE TABLE `student` ( `stuid` VARCHA ...


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);

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

靜態行轉列

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

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

這時大家會發現沒列都出現了重覆的數據，而且只有一列是有值得。其他列都是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;

在這裡解釋一下 ：

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;

最終結果如下 ：

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;

這裡簡單說一下，先生命一個變數賦值為null，把拼接的查詢sql賦值給聲明的變數表中，也可以理解為生成一個臨時表，把查詢出來的數據放到臨時表中。預定義一個語句，並將它賦給 stmt。

存儲過程–動態行轉列

用存儲過程的好處是，方便我們調用，相當於一個函數，其他可能也是類似的查詢不需再重覆寫代碼，直接調存儲過程就好，還能隨心所欲的加上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 ;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • CentOS6.9 Python環境配置（python2.7、pip、virtualenv）
    python2.7 setuptools pip 建立軟鏈接 virtualenv virtualenvwrapper ...
  • 基於macOS+VMware的GNS3內VM上公網
    筆者經常需要做網路實驗，GNS3就是筆者最喜歡用的模擬器，為了便於實驗，需要能從macos上直接ssh登陸模擬出來的vm，並且vm需要上公網。經過研究，已解決此問題，並以此分享出來 ...
  • 基於STM32之UART串口通信協議（一）詳解
    一、前言 1、簡介 寫的這篇博客，是為了簡單講解一下UART通信協議，以及UART能夠實現的一些功能，還有有關使用STM32CubeMX來配置晶元的一些操作，在後面我會以我使用的STM32F429開發板來舉例講解（其他STM32系列晶元大多數都可以按照這些步驟來操作的），如有不足請多多指教。 2、U ...
  • 《Linux就該這麼學》—學習記錄—第0章
    稻盛和夫先生在《活法》中有段一直激勵著我的話，現在轉送給正在閱讀本書的你： “工作馬馬虎虎，只想在興趣和游戲中尋覓快活，充其量只能獲得一時的快感，絕不能嘗到從心底涌出的驚喜和快樂，但來自工作的喜悅並不像糖果那樣—放進嘴裡就甜味十足，而是需要從苦勞與艱辛中滲出，因此當我們聚精會神，孜孜不倦，剋服艱辛後 ...
  • Linux下zookeeper集群搭建
    Linux下zookeeper集群搭建 部署前準備 1. 下載zookeeper的安裝包 http://zookeeper.apache.org/releases.html 我下載的版本是zookeeper 3.4.10。 2. 準備三台伺服器 ip地址為： 3. 檢查jdk版本，安裝jdk環境，j ...
  • mysql 查詢存在A表中而不存在B表中的數據
    有兩張表，學生信息表infolist： 學生姓名錶namelist： 現要查詢出，存在infolist中，而不存在namelist中的學生，語句如下： 查詢結果如下： age有些奇葩，不用在意。 ...
  • 阿裡資料庫性能診斷的利器——SQL執行干預
    在業務資料庫性能問題診斷中，如果發現一個業務性能很差跟某個SQL有關，應用連接池幾乎被該SQL占滿，同時資料庫伺服器上也不堪重負。此時情況很緊急，業務改SQL重發佈已經來不及了，運維能選擇的操作不多。 阿裡資料庫(AliSQL和OceanBase)都支持在運行中干預SQL的執行計劃，以及對問題SQL... ...
  • mycat入門安裝及demo實現
    下載： https://github.com/MyCATApache/Mycat-download 安裝： 直接解壓 運行命令： linux： ./mycat start 啟動 ./mycat stop 停止 ./mycat console 前臺運行 ./mycat restart 重啟服務 ./m ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類