• <noscript id="e0iig"><kbd id="e0iig"></kbd></noscript>
  • <td id="e0iig"></td>
  • <option id="e0iig"></option>
  • <noscript id="e0iig"><source id="e0iig"></source></noscript>
  • MySQL列轉行、行轉列

    標簽: MySQL

    準備數據

    創建表:

    DROP TABLE IF EXISTS tb_score;
    
    CREATE TABLE tb_score(
        id INT(11) NOT NULL auto_increment,
        userid VARCHAR(20) NOT NULL COMMENT '用戶id',
        subject VARCHAR(20) COMMENT '科目',
        score DOUBLE COMMENT '成績',
        PRIMARY KEY(id)
    )ENGINE = INNODB DEFAULT CHARSET = utf8;
    

    插入數據:

    INSERT INTO tb_score(userid,subject,score) VALUES ('001','語文',90);
    INSERT INTO tb_score(userid,subject,score) VALUES ('001','數學',92);
    INSERT INTO tb_score(userid,subject,score) VALUES ('001','英語',80);
    INSERT INTO tb_score(userid,subject,score) VALUES ('002','語文',88);
    INSERT INTO tb_score(userid,subject,score) VALUES ('002','數學',90);
    INSERT INTO tb_score(userid,subject,score) VALUES ('002','英語',75.5);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','語文',70);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','數學',85);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','英語',90);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);
    

    SELECT * FROM tb_score
    建表

    WITH ROLLUP 的用法

    1.WITH ROLLUP:在group分組字段的基礎上再進行統計數據。

    SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    

    在這里插入圖片描述
    上面的結果第是為每個userid進行的統計,最后的一列是對所有的total進行的統計

    MySQL行轉列

    建表
    要將上面的數據轉換成為下面的格式
    在這里插入圖片描述

    1、使用case…when…then 進行行轉列

    SELECT userid,
    SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文',
    SUM(CASE `subject` WHEN '數學' THEN score ELSE 0 END) as '數學',
    SUM(CASE `subject` WHEN '英語' THEN score ELSE 0 END) as '英語',
    SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
    FROM tb_score  GROUP BY userid
    

    2、使用IF() 進行行轉列:

    SELECT userid,
    SUM(IF(`subject`='語文',score,0)) as '語文',
    SUM(IF(`subject`='數學',score,0)) as '數學',
    SUM(IF(`subject`='英語',score,0)) as '英語',
    SUM(IF(`subject`='政治',score,0)) as '政治' 
    FROM tb_score  GROUP BY userid
    

    注意點:

    (1)SUM() 是為了能夠使用GROUP BY根據userid進行分組,因為每一個userid對應的subject="語文"的記錄只有一條,所以SUM() 的值就等于對應那一條記錄的score的值。

    假如userid =‘001’ and subject=‘語文’ 的記錄有兩條,則此時SUM() 的值將會是這兩條記錄的和,同理,使用Max()的值將會是這兩條記錄里面值最大的一個。但是正常情況下,一個user對應一個subject只有一個分數,因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數都可以達到行轉列的效果。

    (2)IF(subject=‘語文’,score,0) 作為條件,即對所有subject='語文’的記錄的score字段進行SUM()、MAX()、MIN()、AVG()操作,如果score沒有值則默認為0。

    3、利用SUM(IF()) 生成列 + WITH ROLLUP 生成匯總行

    SELECT IFNULL(userid,'total') AS userid,
    SUM(IF(`subject`='語文',score,0)) AS 語文,
    SUM(IF(`subject`='數學',score,0)) AS 數學,
    SUM(IF(`subject`='英語',score,0)) AS 英語,
    SUM(IF(`subject`='政治',score,0)) AS 政治,
    SUM(IF(`subject`='total',score,0)) AS total
    FROM(
        SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
        FROM tb_score
        GROUP BY userid,`subject`
        WITH ROLLUP
        HAVING userid IS NOT NULL
    )AS A 
    GROUP BY userid WITH ROLLUP;
    

    在這里插入圖片描述

    三、MySQL列轉行

    創建表:

    CREATE TABLE tb_score1(
        id INT(11) NOT NULL auto_increment,
        userid VARCHAR(20) NOT NULL COMMENT '用戶id',
        cn_score DOUBLE COMMENT '語文成績',
        math_score DOUBLE COMMENT '數學成績',
        en_score DOUBLE COMMENT '英語成績',
        po_score DOUBLE COMMENT '政治成績',
        PRIMARY KEY(id)
    )ENGINE = INNODB DEFAULT CHARSET = utf8;
    

    插入數據:

    INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
    INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
    INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);
    

    在這里插入圖片描述
    結果如下:
    在這里插入圖片描述
    本質是將userid的每個科目分數分散成一條記錄顯示出來。

    SELECT userid,'語文' AS course,cn_score AS score FROM tb_score1
    UNION ALL
    SELECT userid,'數學' AS course,math_score AS score FROM tb_score1
    UNION ALL
    SELECT userid,'英語' AS course,en_score AS score FROM tb_score1
    UNION ALL
    SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
    ORDER BY userid
    

    UNION與UNION ALL的區別:

    1.對重復結果的處理:UNION會去掉重復記錄,UNION ALL不會;

    2.對排序的處理:UNION會排序,UNION ALL只是簡單地將兩個結果集合并;

    3.效率方面的區別:因為UNION 會做去重和排序處理,因此效率比UNION ALL慢很多;

    參考自:https://www.cnblogs.com/yinjw/p/11767282.html

    版權聲明:本文為qq_43081842原創文章,遵循 CC 4.0 BY-SA 版權協議,轉載請附上原文出處鏈接和本聲明。
    本文鏈接:https://blog.csdn.net/qq_43081842/article/details/103632980

    智能推薦

    神奇的Batch Normalization 如果一個模型僅訓練BN層會是什么樣的

    您可能會感到驚訝,但這是有效的。 ? 最近,我閱讀了arXiv平臺上的Jonathan Frankle,David J. Schwab和Ari S. Morcos撰寫的論文“Training BatchNorm and Only BatchNorm: On the Expressive Power of Random Features in CNNs”。 這個主意立刻引起了...

    用Python實現校園通知更新提醒

    前言 這個項目實已經在一個月前已經完成了,一直都想寫一篇博客來總結這個過程中遇到的一些問題。但最近一個月來都比較忙,所以一直拖到了現在。 首先說說起因吧,我沒事的時候,總喜歡依次點開學校主頁、教務處、圖書館以及學院的網站,看看有沒有什么新通知,雖然大多與我無關。恰逢最近正在學Python,經常聽到別人說用Python寫爬蟲很簡單,但自己尚未接觸過爬蟲。于是抱著試一試的心態看了幾篇關于Python爬...

    spring_ioc相關_第一章

    1 spring是一站式框架,在javaee的三層結構中,每一層都提供不提并的解決技術 web層:springMVC service層:spring的ioc dao層:spring的jdbcTemplate 2 javaee為避免兩個類之間出現耦合,則把對象的創建交給spring進行管理,spring的ioc操作:(1)ioc的配置文件方式;(2)ioc注解方式 3 ioc的底層原理使用技術(1)...

    【Python+OpenCV】視頻流局部區域像素值處理-一種特征提取方法

    參考我之前寫的處理圖片的文章:Python+OpenCV實現【圖片】局部區域像素值處理(改進版) 開發環境:Python3.6.0 + OpenCV3.2.0 任務目標:攝像頭采集圖像(例如:480*640),并對視頻流每一幀(灰度圖)特定矩形區域(480*30)像素值進行行求和,得到一個480*1的數組,用這480個數據繪制條形圖,即在逐幀采集視頻流并處理后“實時”顯示采...

    JavaWeb——【前端】——注冊頁面

    頁面效果 實現代碼 注意事項 主要使用的bootstrap樣式 如果想引用,不要直接復制,沒用的。 先介紹下所引用的文件: boostrap的js、bootstrap的css、jquery的js、以及自己編寫的register.css。 因為博主用的thymeleaf語法,所以有th符號。 若要使用時,根據個人情況導入相應的依賴。...

    猜你喜歡

    網站HTTP升級HTTPS完全配置手冊

    本文由葡萄城技術團隊于博客園原創并首發 轉載請注明出處:葡萄城官網,葡萄城為開發者提供專業的開發工具、解決方案和服務,賦能開發者。 今天,所有使用Google Chrome穩定版的用戶迎來了v68正式版首個版本的發布,詳細版本號為v68.0.3440.75,上一個正式版v67.0.3396.99發布于6月13日,自Chrome 68起,當在加載非HTTPS站點時,都會在地址欄上明確標記為&ldqu...

    echarts 自定義儀表盤設置背景圖片

    echarts儀表盤 使用插件 vue-echarts 代碼示例 HTML部分 js部分 效果圖...

    RT-Thread Studio部分定時器時鐘不正確的解決方案

    在昨天的RT-Thread Studio硬件定時器hwtimer在stm32f411上的使用筆記中,遇到了部分定時器速度想象中和實際不一致的情況,具體表現在定時器2、3、4、5、9、10、11都正常,但定時器1要快一倍。 仔細查看代碼,找到了原因。 因為代碼使用的是工程是直接生成的時鐘代碼,實際的時鐘頻率是這樣的: 而實際的定時器時鐘配置代碼如下: 針對F411,去掉其中的宏定義是這樣的: 這里說...

    symfony學習筆記之模板渲染-----twig總結

    參考:https://blog.csdn.net/liebert/article/details/77414217 目錄 一、模板引擎工作原理 二、Twig模板引擎 1.運行環境要求 2.基本API用法 3.設計模板 (1)變量輸出         a.全局變量         b.設置變量 (2)...

    小甲魚Python3學習筆記之第六講(僅記錄學習)

    第六講:python之常用操作符 一、知識點: 0.算術運算符:+,-,*,/,%(取模,即求余數),**(冪運算),//(地板除法,取整除,返回商的整數部分) 備注:①雙斜杠 // 除法總是向下取整。             ②從符點數到整數的轉換可能會舍入也可能截斷,建議使用math....

    精品国产乱码久久久久久蜜桃不卡