mysql实现表的行列转换

本文介绍如何使用SQL进行数据表的行列转换,包括从纵表转横表和横表转纵表的方法。通过具体实例展示了利用CASE语句和UNION ALL操作实现数据结构变化的过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

Sql的行列(纵横表)转换

创建表scores

一、传统的行列转换

纵表转横表

我们要转成的横表是这样子的:

既然这个表只有两列,那么可以根据姓名进行分组。先把姓名拼凑出来,后面的分数我们再想办法。

select 姓名 from scores group by 姓名

结果:

分析:

我们先拿到语文这个科目的分数。既然我们用到了group by 语句,这里肯定要用聚合函数来求分数。

而且我们只需要语文这一科的成绩,分组出来的 一共有 3列 ,分别是 语文、数学、物理  。  那么就需要判断科目来取分数。

这里符合我们需求的 case 语句就登场了。它和c#中switch-case 作用一样。

sql case 语句语法:

case 字段

when 值1 then 结果

when 值2 then 结果2

...

else 默认结果

end

select 姓名,SUM(case 课程 when '语文' then 分数 else 0 end) as 语文 from scores group by 姓名

结果:

既然语文的分数取到了,其他科目改变下条件就可以了。

完整的sql:其中sum(N)是每次用这个sum则数字+N

select 姓名,

SUM(case 课程 when '语文' then 分数 else 0 end) as 语文,

SUM(case 课程 when '数学' then 分数 else 0 end) as 数学,

SUM(case 课程 when '物理' then 分数 else 0 end) as 物理

from scores group by 姓名


横表转纵表

我们也先把张三和李四的语文成绩查出来。

select 姓名,

'语文' as 课程,

语文 as 分数

from scores2

结果:

还有两科的数据怎么办呢? 很简单,我们一个个都查出来,然后用 union all 把他们组合为一张表就可以了。

select 姓名,

'语文' as 课程,

语文 as 分数

from scores2

union all

select 姓名,

'数学' as 课程,

数学 as 分数

from scores2

union all

select 姓名,

'物理' as 课程,

物理 as 分数

from scores2

order by 姓名 desc

结果:

 

 

### 实现VBA结合MySQLExcel数据行列转换的逻辑 在实现VBA结合MySQL对Excel数据进行列转换时,可以分为以下几个部分来完成任务:读取Excel数据、执行列转换、将转换后的数据插入到MySQL数据库中。以下是具体的实现方法和代码示例。 #### 1. 读取Excel数据 使用VBA读取Excel中的数据,并将其存储为二维数组或列形式。可以通过`Range.Value`属性获取单元格区域的数据。 ```vba Sub ReadExcelData() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) Dim lastRow As Long, lastCol As Long lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column Dim excelData As Variant excelData = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value End Sub ``` 此代码段读取了工作中的所有数据,并将其存储在变量`excelData`中[^4]。 #### 2. 执行列转换 通过遍历原始数据,将变为列,列变为。以下是一个简单的行列转换逻辑: ```vba Function TransposeData(excelData As Variant) As Variant Dim rowCount As Long, colCount As Long rowCount = UBound(excelData, 1) colCount = UBound(excelData, 2) Dim transposedData() As Variant ReDim transposedData(1 To colCount, 1 To rowCount) Dim i As Long, j As Long For i = 1 To rowCount For j = 1 To colCount transposedData(j, i) = excelData(i, j) Next j Next i TransposeData = transposedData End Function ``` 此函数接收一个二维数组作为输入,并返回一个新的二维数组,其中行列已互换[^5]。 #### 3. 将转换后的数据插入到MySQL数据库 为了将转换后的数据插入到MySQL数据库中,需要使用ADO(ActiveX Data Objects)库。首先,确保在VBA编辑器中启用“Microsoft ActiveX Data Objects Library”。 以下是连接MySQL并插入数据的代码示例: ```vba Sub InsertIntoMySQL(transposedData As Variant) Dim conn As Object Set conn = CreateObject("ADODB.Connection") Dim connectionString As String connectionString = "DRIVER={MySQL ODBC 8.0 Driver};" & _ "SERVER=your_server;" & _ "DATABASE=your_database;" & _ "UID=your_username;" & _ "PWD=your_password;" conn.Open connectionString Dim sqlQuery As String Dim rowCount As Long, colCount As Long rowCount = UBound(transposedData, 1) colCount = UBound(transposedData, 2) Dim i As Long, j As Long For i = 1 To rowCount sqlQuery = "INSERT INTO your_table (" For j = 1 To colCount - 1 sqlQuery = sqlQuery & "column" & j & ", " Next j sqlQuery = sqlQuery & "column" & colCount & ") VALUES (" For j = 1 To colCount - 1 sqlQuery = sqlQuery & "'" & Replace(transposedData(i, j), "'", "''") & "', " Next j sqlQuery = sqlQuery & "'" & Replace(transposedData(i, colCount), "'", "''") & "')" conn.Execute sqlQuery Next i conn.Close Set conn = Nothing End Sub ``` 此代码段实现了与MySQL数据库的连接,并将转换后的数据逐插入到指定的中[^6]。 #### 4. 综合代码 将上述功能整合到一个完整的VBA宏中: ```vba Sub ExcelToMySQLTranspose() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(1) Dim lastRow As Long, lastCol As Long lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column Dim excelData As Variant excelData = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value Dim transposedData As Variant transposedData = TransposeData(excelData) InsertIntoMySQL transposedData End Sub ``` 此宏实现了从读取Excel数据到行列转换再到插入MySQL数据库的完整流程[^7]。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值