sql编程（行转列）的妙用

原创 于 2024-09-04 08:15:00 发布 · 2.4k 阅读 · 28 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #数据库

目录

前言

1. 数据准备

2. 用法和技巧

2.1 行转列+拼接

2.2 行转列+聚合

3. 总结

提示：本次基于mysql8.0版本进行测试

前言

        对于离线分析报表种，这种需要行转换为列的过程其实还是非常常见的，本文中主要介绍了mysql8.0版本2种方法进行行转化列，其他的方法还有很多得自己去专研一下了。

1. 数据准备

这里导入一批订单数据作为行转列的测试数据，脚本可直接执行（mysql 环境）

脚本：

 # 1.创建一个订单表
create table if not exists orders 
(
sn varchar(20) primary key comment '订单编号',
goods varchar(60) comment '商品名称',
unit int comment '单价',
count int comment '数量',
je double(7,2) comment '销售金额',
time date comment '销售日期',
month varchar(20) comment '销售月份',
uname varchar(20) comment '销售人员'
) comment '订单表';

# 2.初始化表数据
insert into orders(sn,goods,unit,count,time,uname)
values
('sn1000','华为meta60',6999,1,STR_TO_DATE('2024/6/7', '%Y/%m/%d'),'小红')
,('sn1001',	'华为平板meta pad3',	3998,2,STR_TO_DATE('2024/1/8', '%Y/%m/%d'),'小张')
,('sn1002',	'苹果Ipone15',	8698,	2,		STR_TO_DATE('2024/12/9', '%Y/%m/%d'),'小美')
,('sn1003',	'索尼照相机',	10240,	1,		STR_TO_DATE('2024/2/10', '%Y/%m/%d'),'鹏哥')
,('sn1004',	'华为Watch3',	2998,	2,		STR_TO_DATE('2024/4/11', '%Y/%m/%d'),'鹏哥')
,('sn1005',	'华为Watch4',	3999,	3,		STR_TO_DATE('2024/2/24', '%Y/%m/%d'),'鹏哥')
,('sn1006',	'索尼照相机',	10240,	1,		STR_TO_DATE('2024/9/25', '%Y/%m/%d'),'小张')
,('sn1007',	'苹果Ipone15',	8698,	1,		STR_TO_DATE('2024/2/26', '%Y/%m/%d'),'小张')
,('sn1008',	'华为平板meta pad3',	3998,2,STR_TO_DATE('2024/6/27', '%Y/%m/%d'),'小张')
,('sn1009',	'华为meta60',	6999,	3,		STR_TO_DATE('2024/11/28', '%Y/%m/%d'),'小张')
,('sn1010',	'华为meta60',	6999,	1,		STR_TO_DATE('2024/7/29', '%Y/%m/%d'),'小美')
,('sn1011',	'华为pura 70',	5400,	1,		STR_TO_DATE('2024/11/1', '%Y/%m/%d'),'鹏哥')
,('sn1012',	'华为meta60',	6999,	2,		STR_TO_DATE('2024/11/22', '%Y/%m/%d'),'小胖')
,('sn1013',	'华为Watch3',	2998,	2,		STR_TO_DATE('2024/12/3', '%Y/%m/%d'),'小胖')
,('sn1014',	'华为meta60',	6999,	4,		STR_TO_DATE('2024/8/5', '%Y/%m/%d'),'小胖')
,('sn1015',	'华为meta60',	6999,	4,		STR_TO_DATE('2024/10/10', '%Y/%m/%d'),'小胖')
,('sn1016',	'oppo Find x7',	4898,	4,		STR_TO_DATE('2024/3/6', '%Y/%m/%d'),'小红')
,('sn1017',	'华为Watch4',	3999,	1,		STR_TO_DATE('2024/3/7', '%Y/%m/%d'),'小张')
,('sn1018',	'华为navo 10',	3556,	1,		STR_TO_DATE('2024/4/8', '%Y/%m/%d'),'小张')
,('sn1019',	'华为navo 11',	3950,	1,		STR_TO_DATE('2024/4/9', '%Y/%m/%d'),'小张')
,('sn1020',	'华为meta60',	6999,	1,		STR_TO_DATE('2024/3/10', '%Y/%m/%d'),'小胖')
,('sn1021',	'华为平板meta pad3',	3998,1,STR_TO_DATE('2024/3/11', '%Y/%m/%d'),'小胖')
,('sn1022',	'华为非法大师meta60',	19988,2,STR_TO_DATE('2024/8/12', '%Y/%m/%d'),'小张')
,('sn1023',	'索尼照相机',	10240,	1,		STR_TO_DATE('2024/3/13', '%Y/%m/%d'),'小美')
,('sn1024',	'华为pura 70',	5400,	1,		STR_TO_DATE('2024/12/14', '%Y/%m/%d'),'小美')
,('sn1025',	'oppo Find x7',	4898,	1,		STR_TO_DATE('2024/3/15', '%Y/%m/%d'),'小美')
,('sn1026',	'华为meta60',	6999,	2,		STR_TO_DATE('2024/7/16', '%Y/%m/%d'),'鹏哥')
,('sn1027',	'oppo Find x7',	4898,	2,		STR_TO_DATE('2024/3/17', '%Y/%m/%d'),'小张')
,('sn1028',	'华为meta60',	6999,	1,		STR_TO_DATE('2024/9/18', '%Y/%m/%d'),'小美');
# 3.更新销售金额，月份
update orders set 
je=unit*count ,month=DATE_FORMAT(time,'%Y%m');

数据展示：

订单表

2. 用法和技巧

2.1 行转列+拼接

        行转列+拼接：将一行数据转换为一列，并且按照指定分隔符进行拼接起来，形成一个新的列。具有代表的函数是：group_concat()函数

语法：

group_concat([distinct] 要转列字段  [order by asc/des 排序字段] [Separator ‘分隔符’])

说明：

distinct         # 表示去重

要转列的字段        # 多个字段使用逗号“,”分割

order by         # 可以进行排序

Separator        # 每行之间的分隔符，默认为逗号“，”分割

案例：统计每个销售员，销售的商品种类多个商品之间用分号";"隔开，并去重。

效果展示

个人销售商品种类情况表

脚本：

select 
uname,
group_concat(distinct goods separator " ;") as goods_list 
from orders d 
group by uname

注：虽然这个函数在mysql8.0版本中非常好用，但是一般数据比较大时，就不要使用，该函数能转换拼接的范围有限。

2.2 行转列+聚合

        在mysql中PIVOT 子句不能用，之前是准备用他来做该需求的，那么我们只能换一种思路了，利用条件语句 ，聚合函数，高级分组来得到下面的效果。

案例：统计1~12月份，每种商品的销售额，（要求每个月份显示1列，共12列)并进行汇总。

效果展示：

每月商品销售额情况表

     

脚本：

select 
IFNULL(mc,'总计') AS 商品名称,
sum(if(month='202401',je,0) ) as 1月,
sum(if(month='202402',je,0)) as 2月,
sum(if(month='202403',je,0)) as 3月,
sum(if(month='202404',je,0)) as 4月,
sum(if(month='202405',je,0)) as 5月,
sum(if(month='202406',je,0)) as 6月,
sum(if(month='202407',je,0)) as 7月,
sum(if(month='202408',je,0)) as 8月,
sum(if(month='202409',je,0)) as 9月,
sum(if(month='202410',je,0)) as 10月,
sum(if(month='202411',je,0)) as 11月,
sum(if(month='202412',je,0)) as 12月,
sum(if(month is null,je,0)) as 总计
from (
select 
goods as   mc,
month,
sum(je) as je
from orders d 
group by goods,month WITH rollup
having goods is not null 
) as t
group by mc WITH rollup

注：语句中使用里with rollup(高级分组），他的目的是进行小计和汇总的，这里的：

group by goods,month WITH rollup 代表按照goods 进行所有商品进行汇总，并且在每种商品里，按照month月份进行小计的汇总。

3. 总结

        行转列的过程还是很消耗资源的，所有大表建议不要使用group_concat函数，尽量使用第二种方法，使用一些条件语句+聚合函数来进行行的转化。

SQL行转列汇总
dingshi6331的博客
03-04 2023
PIVOT用于将列值旋转为列名（即行转列），在 SQL Server2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 注意：PIVOT、UNPIVOT是SQL Server 2005的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为 90) SQL200...
面试题：SQL 中如何将 多行合并为一行（合并行数据为列）？
最新发布
hixiaoyang的博客
06-05 664
SQL行列转换是数据报表和统计分析的常见需求，本文总结四种多行合并为一行的方法。通过示例演示如何将学生成绩表（姓名、科目、分数）转换为横向展示（姓名、语文、数学）。方法包括：1) 通用CASE WHEN+聚合函数，2) MySQL的GROUP_CONCAT文本拼接，3) SQL Server/Oracle的PIVOT语法，4) PostgreSQL的FILTER特性。对比了各方案的兼容性和特点，通用CASE WHEN适用于所有数据库，而其他方法在特定数据库中语法更简洁。对于动态列名情况，需要结合应用层或存储
SQL中的行列转置
热门推荐
qq_42640067的博客
08-19 2万+
前言 在实际的开发过程中，我们可能会遇到这样的需求：需要将一行数据按照列拆分为多行，或者是需要将多行数据通过扩展列的形式合并为一行。这在SQL上一般称为行列转置，也就是将行转为列，列转为多行。下面我们一起来看看行列转置是如何实现的。 行列转置 1、行转列 行转列实际上是将多行数据按照某个相同的值通过扩展列的形式合并为一行数据。以下通过一个实例来说明。事先准备好3张表：student5表、score5表、subject5表。 student5表数据如下： score5表数据如下： 由于表中数据过多，所以这
SQL行转列
weixin_34072159的博客
03-11 136
　　阅读目录 　　一：前言 　　二：第一次思考 　　三：第二次思考 　　四：第三次思考 　　一：前言 　　在我们做的一些项目中经常会碰到把行转化为列的问题，那么我们今天就来探讨一下，我们怎么样把表1转化为表2的格式 　   　　　　　　　　图一 　　 　　　　　　　　图二 　　二：第一次思考 　　可以看得出来，表2列是由表1的数据行转化而来的，只有Name列是表1中的列，...
sql 行转列
李赛赛的专栏
11-03 1372
一、行转列 PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 1、格式 table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN() ) 2、实例 (1)
sql的行转列
huohua0612的专栏
05-14 332
在弄数据报表的时候，我们常常会用到这个需求。 在mysql中行转列： 新建一个表，学生名字，课程，分数： DROP TABLE IF EXISTS student; create TABLE student( username VARCHAR(20), subjects VARCHAR(20), score int(30) ); INSERT into student VALUES(...
sql 行转列
weixin_33828101的博客
12-18 97
自己想的,性能不知道如何,不过应该影响不大 思路: 1.我们要转的表 name　　FQdState     count 财务会计 未签到 　　　　2思想政治 未签到 　　　　1思想政治 已签到 　　　　1   查询语句 select name,FQdState,count(1)'count' from VMTeetingGuest group by name,FQdState   ...
SQL妙用：动态查询与Identity列解析
"SQL语句妙用与动态SQL的实践指南" 在SQL语言中，动态SQL是一种强大的工具，它允许在运行时构建和执行SQL语句，使得编程更加灵活。以下是一些关于动态SQL的重要知识点： 1. **Exec执行SQL语句**： - 基本的动态...
sql语句妙用指导 sql的使用
08-02
通过以上介绍，可以看出动态SQL和Identity列的使用可以极大地提高SQL编程的灵活性和效率，而掌握基本的查询语句则是进行更复杂操作的基础。希望这些知识点能帮助你在日常开发工作中更加高效地使用SQL。
sql妙用(绝妙的sql语句)
02-16
SQL是Structured Query Language的缩写，是用于管理和处理关系数据库的标准语言。在SQL中，有些技巧和用法能够显著提高你的工作效率，使你...通过不断学习和实践，你可以成为SQL编程的专家，更好地应对各种数据库挑战。
sql语句妙用，各种sql语句的详细用法与讲解
10-06
SQL（Structured Query Language）是用于管理关系数据库的标准语言，它的妙用在于能够高效地处理和检索数据。本篇内容主要涉及SQL语句的详细用法，特别是动态SQL的运用，这对于编写流畅的程序至关重要。 1. 动态SQL...
SQL Developer使用手册
09-14
### SQL Developer 使用手册知识点概述 #### 一、SQL Developer 简介 - **软件版本与发布日期**：本手册介绍的是 Oracle SQL Developer 版本 4.0，发布于 2013 年 12 月。 - **版权信息**：此文档受版权保护，未经...
sql列转行 行转列
weixin_48485132的博客
07-23 1万+
在 SQL 中，转换数据以按列排列的值成为按行排列的值（即所谓的“列转行”或“列转行”）是常见的数据操作需求。这个操作在不同的数据库管理系统中可以通过不同的技术手段来实现。在 SQL 中，行转列（也称为“透视”或“旋转”）的操作是将行数据转换为列数据，这在数据总结和分析中非常有用。不同的数据库系统有不同的方法来实现行转列的操作。操作的数据库，如 Oracle 和 SQL Server，可以直接使用。将多个列的数据合并到一个新的结果集的单一列中。如果你的列数是固定的，可以使用。函数可以实现类似的操作。
SQL 行转列
qq_20236937的博客
04-27 610
（当然如果是在pgsql当中也有crosstab交叉函数可以实现）name为一根后，相同name的后的 其他属性，你可以把它们。sum（case when…）就是对筛选后的数据进行。思考概念：把group by name当成。（sum()、count()…以下主要讲解group by 操作。使用的场景：把查询的后的某些。使用方式（最常用的）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值