【MySQL】Mysql统计之行转列

最新推荐文章于 2024-08-11 15:10:04 发布
最新推荐文章于 2024-08-11 15:10:04 发布
阅读量1.3k 收藏 4
点赞数
分类专栏： 数据库-MySql 文章标签： Mysql 行转列 列转行 GROUP_CONCAT CASE WHEN
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liruichuan/article/details/103052609
版权

事出必有因

几年前一直是做报表相关的业务，自认为对SQL和统计相关的知识还是比较熟练吧，昨天一妹子问我一个统计SQL让我把多行数据汇总转成列来展示，我寻思着这不就是个简单的行转列的问题嘛，上手就写，可是…尴尬了 执行出来的数据不是想要的效果，好在最后我急中生智解决了这个问题不至于颜面扫地，为了避免类似的问题再发生，所以把出来方式新记录一下。

举个栗子

我这里就拿消费者（customer）、订单（order）、商品（product）这个简单的场景来举例子了，首先我们做一下准备工作，先把这几张表创建一下并插入一些测试数据：

#消费者表
DROP TABLE IF EXISTS customer ;
CREATE TABLE customer (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '名称',
  `nick_name` varchar(24) NOT NULL DEFAULT '' COMMENT '昵称',
  `create_at` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
)COMMENT='消费者表';

#插入测试数据
INSERT INTO customer(name,nick_name,create_at)
VALUES
("老王","LW",now()),
("老张","LZ",now()),
("老李","LL",now()),
("老陈","LC",now()),
("老不死","LBS",now())
;

#商品表
DROP TABLE IF EXISTS product ;
CREATE TABLE product (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '商品名称',
  `price` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '商品单价',
  `create_at` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
)COMMENT='商品表';
#插入测试数据
INSERT INTO product(name,price,create_at)
VALUES
("java从入门到放弃",100.0,now()),
("MySql花式删库指南",150.00,now()),
("程序员装逼指南",300.00,now()),
("颈椎病康复指南",20,now())
;

#订单表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `customer_id` int(11) NOT NULL DEFAULT 0 COMMENT '消费者ID',
  `product_id` int(11) NOT NULL DEFAULT 0 COMMENT '商品id',
  `order_no` varchar(32) NOT NULL DEFAULT '' COMMENT '订单号',
  `product_cnt` int(11) NOT NULL DEFAULT 0 COMMENT '商品数量',
	`product_price` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '商品单价',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
  `create_at` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
)COMMENT='订单表';

#插入测试数据
INSERT INTO orders(customer_id,product_id,order_no,product_cnt,product_price,amount,create_at)
VALUES
(1,1,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),2,100.0,2*100,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(2,1,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),3,100.0,3*100,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(3,1,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),1,100.0,1*100,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(4,1,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),5,100.0,5*100,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(1,2,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),7,150.0,7*150,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(2,2,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),5,150.0,5*150,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(3,2,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),8,150.0,8*150,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(4,2,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),3,150.0,3*150,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(1,3,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),9,300.0,9*300,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(2,3,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),6,300.0,6*300,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(3,3,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),34,300.0,34*300,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(4,3,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),6,300.0,6*300,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(1,4,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),4,20.0,4*20,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(2,4,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),23,20.0,23*20,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(3,4,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),12,20.0,12*20,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY)),
(4,4,CONCAT(unix_timestamp(now()),CEILING(RAND()*10000+100000)),1,20.0,1*20,DATE_ADD(now(),INTERVAL FLOOR(RAND()*-100) DAY))
;

主备工作已经就绪，接下来我们先看一下我们的需求：

  1. 查询所有用户近一年每个月的购物情况
  2. 查询所有产品的销售情况，按月统计
  3. 查询所有用户近一年所购产品的情况，按月统计

我们依次对以上需求进行分析，一步一步来解决问题
查询所有用户近一年每个月的购物情况
首先我们查询用户近一年的购物情况，SQL如下：

SELECT
	c.id AS "消费者ID",
	c.`name` AS "消费者姓名",
	DATE_FORMAT(o.create_at, '%Y-%m') as "消费日期",
	SUM(o.amount) as "消费总金额"
FROM
	customer c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY
	c.id,
	c. NAME,
	DATE_FORMAT(o.create_at, '%Y-%m')

查询结果如图：
在这里插入图片描述
我们观察上面的数据发现 有消费的日期只有 2019-08,2019-09,2019-10,2019-11这几个日期，那么我们如何将“消费日期”这一列的每一行数据转成列呢？也就是统计出每个月所有用户的消费情况，Oracle有现成的行转列的函数，但是mysql 貌似就要走曲线救国的道路了，我第一个想到的就是case…when 函数来解决，于是有了下面的SQL：

查询方式一

SELECT
tmp.`消费者ID`,tmp.`消费者姓名`,
SUM(CASE tmp.`消费日期` WHEN '2019-08' THEN tmp.`订单总金额` ELSE 0 END) as "2019-08",
SUM(CASE tmp.`消费日期` WHEN '2019-09' THEN tmp.`订单总金额` ELSE 0 END) as "2019-09",
SUM(CASE tmp.`消费日期` WHEN '2019-10' THEN tmp.`订单总金额` ELSE 0 END) as "2019-10",
SUM(CASE tmp.`消费日期` WHEN '2019-11' THEN tmp.`订单总金额` ELSE 0 END) as "2019-11"
from (
SELECT
	c.id AS "消费者ID",
	c.`name` AS "消费者姓名",
	DATE_FORMAT(o.create_at, '%Y-%m') as "消费日期",
	SUM(o.amount) as "订单总金额"
FROM
	customer c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY
	c.id,c. NAME,DATE_FORMAT(o.create_at, '%Y-%m')
) tmp GROUP BY tmp.`消费者ID`;

统计每个月用户的消费情况结果是出来了,看结果已经达到我们想要的样子

查询方式二
虽然 case…when 的方式可以完成我们的需求，但是子查询貌似看着有点烦，想想其他方式，貌似IF(expr1,expr2,expr3)函数也可以做到类似的事情，我们对以上SQL进行改造：


SELECT
	c.id AS "消费者ID",
	c.`name` AS "消费者姓名",
	sum(IF(DATE_FORMAT(o.create_at, '%Y-%m')='2019-08',o.amount,0.00)) as "2019-08",
	sum(IF(DATE_FORMAT(o.create_at, '%Y-%m')='2019-09',o.amount,0.00)) as "2019-09",
	sum(IF(DATE_FORMAT(o.create_at, '%Y-%m')='2019-10',o.amount,0.00)) as "2019-10",
	sum(IF(DATE_FORMAT(o.create_at, '%Y-%m')='2019-11',o.amount,0.00)) as "2019-11"
FROM
	customer c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY
	c.id,c.`name`;

查询结果：
在这里插入图片描述
对比上面第一种方法，查询结果一致。

查询方式三
以上查询方式看着SQL都很复杂，适用于直接从数据库中导出所用数据（前提是数据量不大，如果数据量太大，要注意索引的问题），还有一个函数可以将多行数据转成一行，GROUP_CONCAT() 函数，具体我们看怎么用：

select 
tmp.`消费者ID`,tmp.`消费者姓名`,
GROUP_CONCAT(tmp.`消费日期`,' 消费金额为:',tmp.`订单总金额`)
from (
SELECT
	c.id AS "消费者ID",
	c.`name` AS "消费者姓名",
	DATE_FORMAT(o.create_at, '%Y-%m') as "消费日期",
	SUM(o.amount) as "订单总金额"
FROM
	customer c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY
	c.id,c.NAME,DATE_FORMAT(o.create_at, '%Y-%m'))tmp GROUP BY tmp.`消费者ID`

查询结果：
在这里插入图片描述GROUP_CONCAT()函数可以将所属于同一分组的多个行转化为一个列返回，且分隔符可以自定义。

我们只实现了一个需求的查询，其他两个查询情况类似(偷懒不想写了)，这里不做过多赘述，有兴趣的可以写一下，欢迎提出指正意见

码农小李子
关注
专栏目录
mysql 行转列并进行计算
u011546137的博客
03-03 556
数据表如下 CREATE TABLE `demo` ( `日期` varchar(255) DEFAULT NULL, `金额` varchar(255) DEFAULT NULL, `用餐人数` varchar(255) DEFAULT NULL, `用餐人员` varchar(255) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREM
mysql 行转列统计_myql 行转列统计查询
weixin_36114355的博客
02-03 238
1 原始数据-- ------------------------------ Table structure for `t_bm_repeat_purchase`-- ----------------------------DROP TABLE IF EXISTS `t_bm_repeat_purchase`;CREATE TABLE `t_bm_repeat_purchase` (`month...
MySql行转列统计
weixin_30853329的博客
10-20 140
方法一: 1 select request_date,user_name, 2 max(case code when '成功' then count else 0 end) 成功数, 3 max(case code when '失败' then count else 0 end) 失败数 4 from (select user_name,request_date,code,...
5分钟搞懂MySQL - 行转列_mysql 性别分完组后,行转列
最新发布
2401_85819563的博客
08-11 407
方一凡 | 70 | 90 | 59 | 200 | 419 || 方一凡 | 差 | 差 | 差 | 419 | 一本 || student_name | 语文 | 数学 | 英语 | 总分 | 结果 || student_name | 语文 | 数学 | 英语 | 特长加分 | 总数 |
Mysql 列转行统计查询 、行转列统计查询
u012107806的专栏
02-04 436
原创http://www.cnblogs.com/lhj588/p/3315876.html -- ---------------------------- -- Table structure for `TabName` -- ---------------------------- DROP TABLE IF EXISTS `TabName`; C
Mysql 列转行统计查询 、行转列统计查询
weixin_33940102的博客
09-11 105
-- ---------------------------- -- Table structure for `TabName` -- ---------------------------- DROP TABLE IF EXISTS `TabName`; CREATE TABLE `TabName` ( `Id` int(11) NOT NULL AUTO_INCREMENT,...
Mysql 行转列 实现中派商家月额统计
04-22
总之，MySQL中的行转列是数据分析过程中的一种重要技巧，尤其对于处理时间序列数据和分类统计时非常实用。通过灵活运用`CASE`和`GROUP BY`，我们可以将原始数据整理成更符合报告需求的形式，从而更好地理解业务情况...
利用MySQL统计一列中不同值的数量方法示例
09-09
在MySQL数据库中，统计一列中不同值的数量是一项常见的数据查询任务，特别是在数据分析和报表生成时。本篇文章将深入探讨几种不同的方法来实现这一目标，这些方法适用于多种情况，无论是一小部分特定值的计数还是对...
mysql-行转列、列转行
08-12
标题“mysql-行转列、列转行”涉及到的是MySQL中的两种主要转换技巧： 1. **行转列（Pivot）**： 行转列通常用于将多行数据转换为单行的多个列。在MySQL中，没有内置的PIVOT函数，但可以通过使用`CASE`语句配合`...
mysql列转行以及年月分组实例
09-09
在MySQL数据库中，数据处理时常会遇到将列转换为行的需求，这通常涉及到行转列（Pivot）操作。在给定的实例中，我们看到了如何实现这一操作以及结合年月进行分组统计的方法。这里我们将详细解释这些知识点。 1. **...
MySQL行转列统计查询、列转行统计查询
brazier820104的专栏
04-06 1113
-- ---------------------------- -- Table structure for `t_test` -- ---------------------------- DROP TABLE IF EXISTS `t_test`; CREATE TABLE `t_test` ( `id` int(11) NOT NULL AUTO_I...
MySQL 列转行统计查询 、行转列统计查询
weixin_44138647的博客
01-02 480
首先我们创建表： -- ---------------------------- -- Table structure for tabname -- ---------------------------- DROP TABLE IF EXISTS `tabname`; CREATE TABLE `tabname` ( `Id` int(11) NOT NULL AUTO_INCREME...
mysql列转行统计查询_Mysql 列转行统计查询 、行转列统计查询
weixin_35740545的博客
01-19 112
最近在群里又碰到一个朋友说起，于是记录一下：假设表名为t.表里有六个字段p1,p2,p3,s1,s2,s3现在想得到p1=100 时s1值的总和p2=100 时s2值的总和p3=100 时s3值的总和创建表：CREATETABLE`t`(`id`int(11)NOTNULLAUTO_INCREMENT,`s1`int(11)DEFAULTNULL,`s2`int(11)DE...
mysql多表统计数查询行转列
u013109978的博客
01-15 1900
今天在统计查询时遇到以下问题，自己做个记录： 部门A在A地区发布一条数据库类型资源，在B地区发布一条文件类型资源。存在db表： db 部门 地区 名称 。。。 部门A 湖南 数据库资源1 。。。 部门A 河北 数据库资源2 。。。 部门B也在A地区发布一条文件类型资源。存在file表如下： file 部门
mysql 行转列分类统计数量
Luxy_wang的博客
03-09 5585
mysql通过行转列根据表中某一字段进行分类统计数量
mysql每日一题0723----- 关联查询然后行转列统计
onemorepoint的博客
07-23 111
hope you can enjoy ???? ! CREATE TABLE T0723A ( ID INT, 机房 VARCHAR(20) ); CREATE TABLE T0723B ( ID INT, 机房ID INT, 主机名称 VARCHAR(20), 主机状态 INT ); INSERT INTO T0723A VALUES (1,'机房A'); INSERT INTO T072...
mysql 行转列查询 用于统计
adolf双手成就梦想
10-27 701
表结构： 行转列查询： SELECT  user_id, (SELECT COUNT(*) FROM daili_workcount AS t1 WHERE `type`=1  AND t1.user_id=c.user_id) as type1, (SELECT COUNT(*) FROM daili_workcount  AS t2 WHERE `type`=2  A
mysql中行转列,转列
weixin_32042959的博客
03-17 509
Oracle 行转列 动态出转换的列2021-03-09 21:58:3210月的第二天，前天写了个Oracle中行转列的pivot的基本使用方法，然后，因为pivot的用法中，正常情况下，我们需要转出多少个列，都得在我们的sql中完完整整地写出，而不能直接在里面写个查询来动态转换。然后，趁着祖国母亲的生日，这几天放假，整理一下处理方法。一、运行环境Win10，Oracle Databa本文分享在...
mysql 中实现行变列
weixin_34417814的博客
05-19 360
前言： mysql行列变化，最难的就是将多个列变成多行，使用的比较多的是统计学中行变列，列变行，没有找到现成的函数或者语句，所以自己写了存储过程，使用动态sql来实现，应用业务场景，用户每个月都有使用记录数录入一张表，一个月一个字段，所以表的字段是动态增长的，现在需要实时统计当前用户使用的总数量，如果你知道有多少个字段，那么可以用select c1+c2+c3+…. From tbname wh...
MySQL行转列操作实例：从数据到分组统计
在MySQL中，行转列（也称为列转换或透视查询）是一种数据操作技巧，用于将一列或多列的数据转换为行，以便于分析和展示。在这个例子中，我们看到的是如何使用不同的方法在SQL Server和MySQL中实现行转列。 首先，...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码农小李子

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值