MySQL按照按照某字段截取部分值后GROUP BY

最新推荐文章于 2023-03-21 16:56:05 发布
转载 最新推荐文章于 2023-03-21 16:56:05 发布 · 4.5k 阅读 · 3 ·
CC 4.0 BY-SA版权
原文链接：https://my.oschina.net/MrBamboo/blog/787755
文章标签：

#数据库 #python

本文介绍了一种使用SQL中的substr函数从特定格式的时间戳中提取年份的方法，并通过一个具体的例子展示了如何根据年份对数据进行分组统计。

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

可能用到的场景:

消费表:

有时间字段201611201230是年月日时分,但是统计是可能要按照年或者月来查询,那就可以使用substr来完成

SELECT SUM(a.ss) FROM(
SELECT substr(TALLY_TIME,1,4) tt,AMOUNT_PAID ss,FIRST_CLASSIFY_NAME FROM tally) a 
GROUP BY a.tt

这样就可以截取时间到年.从而算出每一年的统计数据

转载于:https://my.oschina.net/MrBamboo/blog/787755

字符串切割 group by
紫蝶侠的博客
06-07 836
sql
sql：mysql：函数：列转行（转为一个字段）：GROUP_CONCAT，分组后将某列的值合并成一行
不花的花和尚的博客
01-17 1349
CREATE TABLE `testmysql_group_concat` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `userid` varchar(50) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=I...
by截取字段 group_数据库之groupby、having、截取字符串
weixin_29476659的博客
02-23 1318
最近在做一些笔试题时经常遇到sql语句的题目，考查最多的就是groupby和having了，现根据一些例子来总结一下。在介绍GROUP BY 和 HAVING 子句前，先讲讲sql语言中一种特殊的函数：聚合函数，例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。SELECT SUM(population) FROM bbc这里的SUM作用在...
关于sql字符串字段的模糊查询与字符串字段里筛选部分值的记录的解决办法
weixin_30699443的博客
09-08 413
一、先说筛选字符串字段中部分值的记录。 例如我有一个KKBH（卡口编号）字段，这是一个字典字段(对应另一个实体表（卡口表）的编号字段)。这个字段的值保存所属卡口值域{01，02，03} 本来想到的是通过or来实现，这样需要动态生成SQL语句。 后来想到一个办法用charindex搜索字符串的办法。将所有的要查的卡口编号组成类似'01@02'这样待查字符串。sql查询时通过charindex筛...
Mysql将查询结果某个字段以逗号分隔，使用group_concat函数可以实现（配合group by使用）
万事俱备，就差一个程序员了
10-14 1712
示例：SELECT Id, GROUP_CONCAT(Name SEPARATOR ',') Names FROM some_table GROUP BY id。
mysql根据某个字段的一部分group by 分组查询
Camellia919的博客
06-10 2万+
通常情况下有些记录编码值，有父级关系，分类关系 通常会包含某些固定内容，那么在查询这类数据时，可以使用： 1. SUBSTRING_INDEX（字段名，‘分割符’， 第几个分割符（以1开始）） 函数 截取字符串的函数 2.固定开头 left(字段名，长度) 函数 是一个字符串函数，它返回具有指定长度的字符串的左边部分 3.固定结尾 right(字段名，长度) 函数 ...
MySQL和Oracle的字段截取问题.txt
05-26
解决截取问题的思路必须符合业务逻辑和字段的排列逻辑，考虑必须长远，脚本类的代码如果只是简单的学习，可以通过事务控制，防止误删数据，查询语句比较复杂的可以考虑分层查询，并减少GROUP BY的使用来提高SQL执行...
MySQL将一个字段中以逗号分隔的取出来形成新的字段实现
12-14
在MySQL数据库中，经常遇到需要处理以逗号分隔的字段值的情况，这些字段通常用于存储多值数据，比如在本例中，`related_shop_ids`字段存储了多个商店ID，用逗号分隔。本篇文章将详细讲解如何将这样的字段拆分成多个...
Mysql5.7中使用group concat函数数据被截断的问题完美解决方法
09-09
在MySQL 5.7中，`GROUP_CONCAT`函数用于将分组后的多个行合并成一个字符串，通常在聚合查询中使用。然而，当使用此函数时，可能会遇到数据被截断的问题，尤其是在处理较长的字符串时。这个问题源于MySQL的一个系统...
java的List流操作
weixin_44486583的博客
02-08 4151
java的list流操作，在使用过程中逐渐补充
SQL知识点
weixin_44476371的博客
07-15 432
笔记 1.group_concat(X,Y) 其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。 2.substr(X,Y,Z) 或 substr(X,Y) 其中X是要截取的字符串。Y是字符串的起始位置（第一个字符起始位置为1），Z是要截取字符串的长度。 3.UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 更新多列用逗号连接不能用AND 连接 4.ALTER table titles_test rename to title
mysql分组获取其他字段_sqlserver group by后获取其他字段（多种方法）
weixin_39776298的博客
01-28 5061
大家都知道用group by的话，select 后面指定的字段必须与group by后面的一致。group by 只有个别字段，如果拿出其他未分组的字段信息呢？在网上搜了下，总结如下：使用了group by 之后，就要求select后面的字段包含在group by 或聚合函数里面，这时如果想读取其它字段则无法实现。将你需要的字段放进max或min函数中，max:支持字符类型、数字类型。select...
MySQL查询GROUP BY日/月/年
p15097962069的博客
01-03 8707
有可能我做一个简单的查询来计算我在一个确定的时间段内有多少记录，如年，月或日，有一个TIMESTAMP字段，如： SELECT COUNT(id) FROM stats WHERE record
mysql group by分组，根据一个字段分组 ，又想得到另一个字段的最大或者最小。如何解决。
qq_19551571的博客
02-19 6353
mysql group by分组，根据一个字段分组 ，又想得到另一个字段的最大或者最小。如何解决。
mysql中group by 的用法解析
weixin_30894389的博客
05-22 5593
1. group by的常规用法 group by的常规用法是配合聚合函数，利用分组信息进行统计，常见的是配合max等聚合函数筛选数据后分析，以及配合having进行筛选后过滤。 假设现有数据库表如下： 表user_info,id主键,user_id唯一键 CREATE TABLE `user_info` ( `id` INT(11) NOT NULL AU...
P40 group by的使用
weixin_54671087的博客
11-26 210
2.group by的使用 #需求：查询各个部门的平均工资，最高工资 SELECT department_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id #需求：查询各个job_id的平均工资 SELECT job_id,AVG(salary) FROM employees GROUP BY job_id; #需求：查询各个department_id,job_id的平均工资 SELECT department_id,job
GROUP BY 使用方法详解
热门推荐
问心一剑的博客
03-21 5万+
主要讲述了SQL Server 中 group by 语句的语法和group by 如何和聚合函数以及where和having在一起的使用方法。
MySQL入门 - 数据分组之 group by
汤圆学Java
08-31 5816
group by 用来对数据进行分组，即按照指定的字段或者表达式进行分组；group by 作用于 where 之后，order by 之前：即先通过 where 过滤数据，再对过滤后的数据中进行分组，分组的依据就是字段 column的值，即不同的column字段值，分成不同的组；然后再根据column字段值进行降序排列group by 可以用来对数据进行分组（即去重），依据是指定字段或者表达式（即对字段进行函数操作，比如上面介绍的substring()函数）......
mysql的REGEXP_SUBSTR函数截取字段中的所有数字
最新发布
04-22
<think>好的，用户现在想用MySQL的REGEXP_SUBSTR函数来提取字段中的所有数字。之前他问过如何提取非数字，现在反过来要数字了。我需要仔细分析他的需求，并结合之前的回答和提供的参考资料来给出解决方案。 首先，回顾之前的回答，用户已经了解如何使用REGEXP_SUBSTR配合递归CTE来提取所有非数字字符。现在需要类似的思路，但目标换成数字。核心的正则表达式需要从匹配非数字改成匹配数字，也就是将[^0-9]+改为[0-9]+。不过要注意，用户这次是要提取所有数字，而不仅仅是第一个匹配项。 用户提供的参考资料中，引用[1]详细说明了REGEXP_SUBSTR的基本用法，引用[2]和[3]提到了使用递归CTE或CONNECT BY LEVEL的方法来拆分字符串，这可能适用于提取多个匹配项。但MySQL不支持CONNECT BY，所以需要继续使用递归CTE的方法，就像之前处理非数字那样。 接下来需要确定如何调整正则表达式。原来的正则表达式是[^0-9]+，现在应该是[0-9]+，用来匹配一个或多个连续的数字。同时，用户可能需要将所有匹配的数字连接起来，形成完整的数字字符串。 在之前的回答中，递归CTE通过不断增加occurrence参数来遍历所有匹配的非数字片段。同样的方法可以应用在这里，只是正则表达式改为匹配数字。此外，用户可能需要处理字符串中的多个数字片段，例如“A12B34C56”中的12、34、56，需要将它们提取并合并为123456或者保留原样，这需要根据用户需求调整。如果用户希望保留每个数字片段的顺序，合并的话就需要用GROUP_CONCAT，否则可能需要单独列出每个数字部分。 需要注意的是，递归CTE在MySQL中的限制，尤其是处理长字符串时可能会有性能问题。需要提醒用户这一点，并提供优化建议，比如预过滤或限制匹配长度。 另外，用户可能混淆了REGEXP_SUBSTR和REGEXP_REPLACE的功能。例如，直接替换非数字字符可能更简单，但用户明确要求使用REGEXP_SUBSTR，所以需要坚持用该函数的方法。但也可以提供替代方案，比如用REGEXP_REPLACE删除非数字字符，这样更高效，但可能不符合用户的具体需求。 还要考虑特殊场景，比如字符串中没有数字的情况，或者数字之间有其他字符分隔的情况。例如，在示例中的“Ab12#c%78”提取数字应该得到“1278”还是“12”和“78”？根据用户的问题描述，“截取字段中的所有数字”，可能是指提取所有数字字符并合并，因此需要用递归CTE提取每个数字段再连接。 最后，确保回答结构清晰，分步骤说明，包括正则表达式设计、递归CTE方法、参数说明、示例以及优化建议。同时，生成的相关问题需要与当前主题相关，比如性能优化、替代方法等。</think>以下是在 MySQL 中提取所有数字的完整解决方案： ### 一、核心正则表达式设计 $$ [0-9]+ $$ - **`[0-9]`**：匹配所有数字字符（等价于 $\\d$） - **`+`**：连续匹配 1 次或多次 ### 二、递归 CTE 提取全部数字 ```sql WITH RECURSIVE num_extract AS ( SELECT 'A1B23CD456' AS origin_str, REGEXP_SUBSTR('A1B23CD456', '[0-9]+', 1, 1) AS num_part, 1 AS occurrence UNION ALL SELECT origin_str, REGEXP_SUBSTR(origin_str, '[0-9]+', 1, occurrence + 1), occurrence + 1 FROM num_extract WHERE REGEXP_SUBSTR(origin_str, '[0-9]+', 1, occurrence + 1) IS NOT NULL ) SELECT GROUP_CONCAT(num_part SEPARATOR '') AS all_numbers FROM num_extract WHERE num_part IS NOT NULL; -- 返回 '123456' [^1] ``` ### 三、关键参数说明 | 参数 | 作用 | 示例 | |------|------|------| | `position=2` | 从第 2 个字符开始搜索 | `REGEXP_SUBSTR('X1Y2Z3', '[0-9]+', 2)` → `'2'` | | `occurrence=3` | 获取第 3 个匹配项 | `REGEXP_SUBSTR('A1B2C3D4', '[0-9]+', 1, 3)` → `'3'` | | `match_type='i'` | 对字母部分忽略大小写 | `REGEXP_SUBSTR('a1B2c3', '[0-9]+', 1, 1, 'i')` → `'1'` | ### 四、特殊场景处理 #### 1. 合并分散的数字 输入：`'Ab12#c%78'` → 输出：`'1278` ```sql -- 使用上述递归 CTE 的 GROUP_CONCAT 方案 ``` #### 2. 保留数字分隔符 输入：`'1-800-555-1234'` → 输出：`['1','800','555','1234']` ```sql SELECT REGEXP_SUBSTR(phone_number, '[0-9]+', 1, level) FROM dual CONNECT BY LEVEL <= REGEXP_COUNT(phone_number, '[0-9]+'); -- 需改用 MySQL 的递归 CTE 实现 [^2] ``` ### 五、性能优化方案 1. **预过滤加速**： ```sql WHERE col REGEXP '[0-9]' -- 先过滤含数字的记录 ``` 2. **限制匹配长度**： ```sql [0-9]{1,10} -- 最多匹配 10 位连续数字 ``` 3. **虚拟列缓存**： ```sql ALTER TABLE orders ADD COLUMN numeric_part VARCHAR(20) GENERATED ALWAYS AS (REGEXP_REPLACE(order_code, '[^0-9]', '')); ``` ### 六、替代方案对比 | 方法 | 优点 | 缺点 | |------|------|------| | 递归 CTE | 精确控制每个匹配项 | 处理长字符串性能下降 | | REGEXP_REPLACE | 单次操作高效 | 会丢失数字位置信息 | | 多次调用 SUBSTRING | 兼容低版本 MySQL | 代码复杂度高 |
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值