快捷導(dǎo)航

MySQL動(dòng)態(tài)列轉(zhuǎn)行的實(shí)現(xiàn)示例

更新時(shí)間：2024年11月10日 11:46:53 作者：#看心情

本文介紹了如何在MySQL中實(shí)現(xiàn)動(dòng)態(tài)列轉(zhuǎn)行的功能,通過使用格式化日期、計(jì)數(shù)函數(shù)、分組、存儲(chǔ)過程、分組合并函數(shù)和SQL拼接等技巧,可以將動(dòng)態(tài)列轉(zhuǎn)換為行,從而更好地進(jìn)行數(shù)據(jù)分析和展示,感興趣的可以了解一下

介紹??

在實(shí)際的數(shù)據(jù)庫(kù)查詢中，有時(shí)候我們需要將表中的動(dòng)態(tài)列（即列數(shù)不固定）轉(zhuǎn)換為行，以便更好地進(jìn)行數(shù)據(jù)分析和展示。在MySQL中，可以通過使用一些技巧和函數(shù)來實(shí)現(xiàn)動(dòng)態(tài)列轉(zhuǎn)行的功能。本文將介紹怎么實(shí)現(xiàn)MySQL動(dòng)態(tài)列轉(zhuǎn)行。

初始表

首先，假設(shè)我們有一個(gè)表格 users，其中需要?jiǎng)討B(tài)的列 create_time，我們希望將該列轉(zhuǎn)換為行。下面是一個(gè)示例表格的結(jié)構(gòu)：

# 創(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 '電話號(hào)碼',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
    is_deleted  INT      DEFAULT 0 COMMENT '邏輯刪除(1:已刪除，0:未刪除)'
) COMMENT '用戶信息表';

# 插入數(shù)據(jù)
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');

想要的效果：

通過格式化日期+計(jì)數(shù)函數(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í)行結(jié)果：

在這里插入圖片描述

顯然這并不是我們想要的效果。這時(shí)候就需要用到動(dòng)態(tài)列轉(zhuǎn)行了。

通過格式日期+求和函數(shù) 實(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í)行結(jié)果：

在這里插入圖片描述

這樣就達(dá)到我們要的效果了。但是有局限性，如果在加一個(gè)日期就需要改SQL。

通過存儲(chǔ)過程+分組合并函數(shù)+SQL拼接實(shí)現(xiàn)

# 設(shè)置結(jié)束分隔符
DELIMITER $$
# 判斷定義的存儲(chǔ)過程是否存在，存在刪除，以防存儲(chǔ)過程存在報(bào)錯(cuò)
DROP PROCEDURE IF EXISTS pro$$ 
# 創(chuàng)建存儲(chǔ)過程
CREATE PROCEDURE pro () BEGIN
  # 定義一個(gè)變量
  SET @SQL = NULL;
	# 把查詢的日期賦給變量
	# 這里需要將日期格式化一下, 我要的格式是yyyy/MM/dd,
	# 而數(shù)據(jù)給我們的是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;
	# 注意：如果運(yùn)行時(shí)報(bào)錯(cuò)可以執(zhí)行
	# SELECT @SQL;
	# 檢查拼接的SQL是否正確
	# 拼接sql
	SET @SQL = concat( 'select ', @SQL, ' from users' );
	# 預(yù)處理語(yǔ)句
	PREPARE stmt FROM	@SQL;
	# 執(zhí)行
	EXECUTE stmt;
	# 銷毀
	DEALLOCATE PREPARE stmt;
# 結(jié)束
END $$ 

 # 調(diào)用存儲(chǔ)過程
CALL pro ();

為了方便測(cè)試在插入一筆數(shù)據(jù)

INSERT INTO users (username, password, nickname, phone, create_time)
VALUES ('test', 'test', 'test', '18955554844', '2023-05-08 22:48:11');

執(zhí)行結(jié)果：

在這里插入圖片描述

通過動(dòng)態(tài)的SQL拼接這種方法可以幫助我們更好地處理動(dòng)態(tài)列的數(shù)據(jù)，方便進(jìn)行后續(xù)的數(shù)據(jù)分析和展示。這樣就滿足我們的場(chǎng)景了。

到此這篇關(guān)于MySQL動(dòng)態(tài)列轉(zhuǎn)行的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL動(dòng)態(tài)列轉(zhuǎn)行內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: