MySQL動態(tài)列轉行的實現(xiàn)示例
更新時間：2024年11月10日 11:46:53 作者：#看心情
本文介紹了如何在MySQL中實現(xiàn)動態(tài)列轉行的功能,通過使用格式化日期、計數(shù)函數(shù)、分組、存儲過程、分組合并函數(shù)和SQL拼接等技巧,可以將動態(tài)列轉換為行,從而更好地進行數(shù)據分析和展示,感興趣的可以了解一下
介紹??
在實際的數(shù)據庫查詢中，有時候我們需要將表中的動態(tài)列（即列數(shù)不固定）轉換為行，以便更好地進行數(shù)據分析和展示。在MySQL中，可以通過使用一些技巧和函數(shù)來實現(xiàn)動態(tài)列轉行的功能。本文將介紹怎么實現(xiàn)MySQL動態(tài)列轉行。
初始表
首先，假設我們有一個表格 users，其中需要動態(tài)的列 create_time，我們希望將該列轉換為行。下面是一個示例表格的結構：
# 創(chuàng)建用戶表
DROP TABLE IF EXISTS users;
CREATE TABLE users
(
id INT PRIMARY KEY auto_increment COMMENT '主鍵',
username VARCHAR(30) NOT NULL COMMENT '用戶名',
password VARCHAR(30) NOT NULL COMMENT '密碼',
nickname VARCHAR(30) COMMENT '昵稱',
phone VARCHAR(11) COMMENT '電話號碼',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
is_deleted INT DEFAULT 0 COMMENT '邏輯刪除(1:已刪除，0:未刪除)'
) COMMENT '用戶信息表';
# 插入數(shù)據
INSERT INTO users (username, password, nickname, phone, create_time)
VALUES ('admin', 'admin', '張三', '18955554444', '2023-05-01 22:48:11'),
('root', 'root', '李四', '17755624235', '2023-05-02 22:48:11'),
('lisi', 'lisi', '王五', '15989654123', '2023-05-03 22:48:11'),
('lucky', 'lucky', '趙六', '19956852548', '2023-05-04 22:48:11'),
('admin2', 'admin', '張三', '18955554444', '2023-05-01 22:48:11'),
('root2', 'root', '李四', '17755624235', '2023-05-02 22:48:11'),
('lisi2', 'lisi', '王五', '15989654123', '2023-05-01 22:48:11'),
('lucky2', 'lucky', '趙六', '19956852548', '2023-05-01 22:48:11');
想要的效果：
通過 格式化日期+計數(shù)函數(shù)+分組 實現(xiàn)
select DATE_FORMAT(create_time,'%Y/%m/%d') as create_date, count(*) as sum from users group by create_date
執(zhí)行結果：
顯然這并不是我們想要的效果。 這時候就需要用到動態(tài)列轉行了。
通過 格式日期+求和函數(shù) 實現(xiàn)
SELECT SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/01' ) AS '2023/05/01', SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/02' ) AS '2023/05/02', SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/03' ) AS '2023/05/03', SUM( DATE_FORMAT( create_time, '%Y/%m/%d' ) = '2023/05/04' ) AS '2023/05/04' FROM users
執(zhí)行結果：
這樣就達到我們要的效果了。但是有局限性，如果在加一個日期就需要改SQL。
通過 存儲過程+分組合并函數(shù)+SQL拼接 實現(xiàn)
# 設置結束分隔符 DELIMITER $$ # 判斷定義的存儲過程是否存在，存在刪除，以防存儲過程存在報錯 DROP PROCEDURE IF EXISTS pro$$ # 創(chuàng)建存儲過程 CREATE PROCEDURE pro () BEGIN # 定義一個變量 SET @SQL = NULL; # 把查詢的日期賦給變量 # 這里需要將日期格式化一下, 我要的格式是yyyy/MM/dd, # 而數(shù)據給我們的是yyyy-MM-dd HH:mm:ss SELECT GROUP_CONCAT( DISTINCT CONCAT( 'SUM(DATE_FORMAT(create_time, \'%Y/%m/%d\') = ''', DATE_FORMAT( create_time, '%Y/%m/%d' ), ''') AS ''', DATE_FORMAT( create_time, '%Y/%m/%d' ), '''' ) ) INTO @SQL FROM users; # 注意：如果運行時報錯可以執(zhí)行 # SELECT @SQL; # 檢查拼接的SQL是否正確 # 拼接sql SET @SQL = concat( 'select ', @SQL, ' from users' ); # 預處理語句 PREPARE stmt FROM @SQL; # 執(zhí)行 EXECUTE stmt; # 銷毀 DEALLOCATE PREPARE stmt; # 結束 END $$ # 調用存儲過程 CALL pro ();
為了方便測試在插入一筆數(shù)據
INSERT INTO users (username, password, nickname, phone, create_time)
VALUES ('test', 'test', 'test', '18955554844', '2023-05-08 22:48:11');
執(zhí)行結果：
通過動態(tài)的SQL拼接這種方法可以幫助我們更好地處理動態(tài)列的數(shù)據，方便進行后續(xù)的數(shù)據分析和展示。這樣就滿足我們的場景了。
到此這篇關于MySQL動態(tài)列轉行的實現(xiàn)示例的文章就介紹到這了,更多相關MySQL動態(tài)列轉行內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這段文章詳細介紹了數(shù)據庫事務的ACID特性,重點闡述了原子性和持久性的實現(xiàn)機制,包括CommitLogging和WAL機制,通過具體案例和代碼演示,深入解析了數(shù)據庫如何確保事務的正確執(zhí)行,感興趣的朋友跟隨小編一起看看吧2026-05-05
下面小編就為大家?guī)硪黄猧nnodb系統(tǒng)表空間維護方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
這篇文章主要介紹了mysql下載與安裝過程，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-03-03
本文主要介紹了MySQL表的碎片整理和空間回收的方法，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-09-09
本文主要介紹了mysql遠程跨庫聯(lián)合查詢的示例，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-03-03
這篇文章主要為大家詳細介紹了MySQL的InnoDB存儲引擎的數(shù)據頁結構，，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助2022-03-03
本文描述了mysql嵌套查詢和聯(lián)表查詢優(yōu)化的方法，有些情況可以使用這種優(yōu)化方法，而有些情況，這種方法就無能為力了2013-02-02
這篇文章主要介紹了MySql?怎么查出符合條件的最新的數(shù)據行,本文通過示例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-07-07
這篇文章主要為大家詳細介紹了Navicat無法連接MySQL報錯1251的解決方案,文中解決方法介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下2023-12-12
本文針對?MySQL?數(shù)據庫的?InnoDB?存儲引擎，介紹其中索引的實現(xiàn)以及索引在慢?SQL?優(yōu)化中的作用，本文主要討論不同場景下索引生效與失效的原因，感興趣的小伙伴可以跟著小編一起來探討2023-05-05