mysql 三天 分组查询_mysql group by分组查询

最新推荐文章于 2024-07-31 23:19:06 发布
最新推荐文章于 2024-07-31 23:19:06 发布
阅读量496 收藏
点赞数
文章标签： mysql 三天 分组查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42499004/article/details/113564460
版权
本文详细介绍了在MySQL中如何使用`GROUP BY`和`HAVING`进行分组查询，强调了分组查询的原则和注意事项。通过实例展示了如何获取满足条件的第一条和最后一条信息，以及如何在没有`PARTITION BY`的情况下实现类似功能。同时，讨论了`IN`、`EXISTS`和`JOIN`在不同场景下的效率比较，并提供了获取指定位置数据的方法，如利用`GROUP_CONCAT`和`SUBSTRING_INDEX`组合获取前N条记录。
摘要由CSDN通过智能技术生成

分组的SQL语句有2个：

group by 和分组聚合函数实现 partition by (oracle和postgreSQL中的语句)功能

group by + having 组合赛选数据

注意：having 条件的字段必须在前面查询赛选存在，否则语法错误

错误格式：

SELECT MAX(ID),U_ID FROM mlzm_comments GROUP BY U_ID HAVING Data_Status >0

正确格式：

SELECT MAX(ID),U_ID,Data_Status FROM mlzm_comments GROUP BY U_ID HAVING Data_Status >0

group by强调的是一个整体，就是组，只能显示一个组里满足聚合函数的一条记录， partition by 在整体后更强调个体，能显示组里所有个体的记录。

#实际需求，获取满足条件第一条信息或最后一条信息

步骤拆解：

#步骤一：找出所有符合第一条件条件的数据,默认排序是按主键索引升序排列,这里按u_id 字段排序方便审阅

SELECT

a.ID,a.U_ID

FROM

mlzm_content a

WHERE

a.Data_Status= 2ORDER BY a.U_ID,a.ID ASC;

304d01da5267593964da918bfce6d005.png

#步骤2：利用group by 和max()、min()函数，对符合第一条件的数据进行分组，并获取当前分组中最小和最大的，注意当前结果集中的id字段不一定是最小的，若想要最小或最大id,需要对表a先进行排序处理

#未对表a 进行排序

SELECT

b.ID,b.U_ID,MIN(b.ID),MAX(b.ID)

FROM

(

SELECT

a.ID,a.U_ID

FROM

mlzm_content a

WHERE

a.Data_Status= 2) AS b

GROUP BY b.U_ID;

上面的语句等效于

#优化处理,但这样的数据无法保证a.ID 排序的有效性

SELECT

a.ID,a.U_ID,MIN(a.ID),MAX(a.ID)

FROM

mlzm_content a

WHERE

a.Data_Status= 2GROUP BY a.U_ID;

732aaa4c57c3a4b28648c734324f1d1e.png

在这可以明确看出，所得的结果集中，当前的id 并非最小的也非最大的(如第1条显示的，当前id 为 31550 而最小的是 30768 最大的为38849)，因此这种情况id字段不能作为后面的赛选条件

接下来用未排序的ID 字段作为依据查找的数据也并想要的

#以未排序的id字段作为参考依据，结果并非想要的

SELECT

c.ID,c.U_ID,b.IDas b_id,b.U_ID asb_uid ,b.min_id,b.max_id

FROM

mlzm_content AS c

INNER JOIN (

SELECT

a.ID,

a.U_ID,

MIN(a.ID)asmin_id,

MAX(a.ID)asmax_id

FROM

mlzm_contentasa

WHERE a.Data_Status=2GROUP BY

a.U_ID

ORDER BY a.U_ID

) AS b ON c.ID=b.ID

3008aa45187c9744b071cec22bcd648c.png

这种情况想要得到最小/最大的的一条信息，需要以min_id /max_id作为参考依据

#从未排序结果集中获取最小的一条信息

SELECT

c.ID,c.U_ID,b.IDas b_id,b.U_ID asb_uid ,b.min_id,b.max_id

FROM

mlzm_content AS c

INNER JOIN (

SELECT

a.ID,

a.U_ID,

MIN(a.ID)asmin_id,

MAX(a.ID)asmax_id

FROM

mlzm_contentasa

WHERE a.Data_Status=2GROUP BY

a.U_ID

ORDER BY a.U_ID

) AS b ON c.ID=b.min_id

#从未排序结果集中获取最大的一条信息

SELECT

c.ID,c.U_ID,b.IDas b_id,b.U_ID asb_uid ,b.min_id,b.max_id

FROM

mlzm_content AS c

INNER JOIN (

SELECT

a.ID,

a.U_ID,

MIN(a.ID)asmin_id,

MAX(a.ID)asmax_id

FROM

mlzm_contentasa

WHERE a.Data_Status=2GROUP BY

a.U_ID

ORDER BY a.U_ID

) AS b ON c.ID=b.max_id

最小的一条结果

9b552721d517942d759ca43bb85b8876.png

耗时：0.0310 秒

最大的一条结果

9b08ad17d6a44cc17c4625b9b8cafde7.png

先排序后再获取数据

#对表a 进行先排序处理

SELECT

b.ID,b.U_ID,MIN(b.ID),MAX(b.ID)

FROM

(

SELECT

a.ID,a.U_ID

FROM

mlzm_content a

WHERE

a.Data_Status= 2ORDER BY a.ID ASC

) AS b

GROUP BY b.U_ID;

cb870d092eecf62bdc9f95ccdbc17934.png

先进行排序后可以看出当前的id 和min(b.id)一致，现在可用id字段作为赛选条件

接下来用排序后得到的id 和min_id 来进行获取分组中最小的一条信息

#用排序后得到的id作为判断依据

SELECT

d.ID,

d.U_ID,

c.ID AS c_id,

c.U_ID AS c_uid,

c.min_id,

c.max_id

FROM

mlzm_content AS d

INNER JOIN (

SELECT

b.ID,

b.U_ID,

MIN(b.ID) AS min_id,

MAX(b.ID) AS max_id

FROM

(

SELECT

a.ID,

a.U_ID

FROM

mlzm_content a

WHERE

a.Data_Status= 2ORDER BY

a.ID ASC

) AS b

GROUP BY

b.U_ID

) AS c ON d.ID=c.ID

#用min_id 作为依据

SELECT

d.ID,

d.U_ID,

c.ID AS c_id,

c.U_ID AS c_uid,

c.min_id,

c.max_id

FROM

mlzm_content AS d

INNER JOIN (

SELECT

b.ID,

b.U_ID,

MIN(b.ID) AS min_id,

MAX(b.ID) AS max_id

FROM

(

SELECT

a.ID,

a.U_ID

FROM

mlzm_content a

WHERE

a.Data_Status= 2ORDER BY

a.ID ASC

) AS b

GROUP BY

b.U_ID

) AS c ON d.ID= c.min_id

得到的结果集相同，因此先排序处理后id是可作为判断依据的否则只能用min_id作为判断依据

03f75b84dfb0cdff0fa28a369ebb4609.png

耗时：0.0410 秒

结论：先进行排序处理,关键字段可做判断依据，否则需要用获取的max(column_name)或min(column_name)作为判断依据。

但先进行排序会比不排序耗费的时间多，因此排序直接使用max(column_name)或min(column_name)作为判断依据处理要高效点。

错误的筛选方式：

#该方式判断是错误的，因为 结果集b中并未进行条件赛选，因此的到的结果中的min_id 对应的数据可能并不满足Data_Status=2的条件，最后做判断的时候这些数据会被排除掉。

SELECT

c.ID,c.U_ID,b.IDas b_id,b.U_ID asb_uid ,b.min_id,b.max_id

FROM

mlzm_content AS c

INNER JOIN (

SELECT

a.ID,

a.U_ID,

MIN(a.ID)asmin_id,

MAX(a.ID)asmax_id

FROM

mlzm_contentasa

GROUP BY

a.U_ID

) AS b ON c.ID=b.min_id WHERE c.Data_Status=2

这是错误的判断方式，谨记啊

这样得到的结果集要少于真实的数据集。

除了(INNER) JOIN 外也可通过 in 和 exists 来获取

#用in 方式

SELECT

c.ID,c.U_ID

FROM

mlzm_content AS c

WHERE

c.IDin(

SELECT

MIN(a.ID)asmin_id

FROM

mlzm_contentasa

WHERE a.Data_Status=2GROUP BY

a.U_ID

)

ORDER BY U_ID

耗时：0.0410 秒

#用exists 方式获取

SELECT

c.ID,c.U_ID

FROM

mlzm_content AS c

WHERE

exists

(

SELECT* from(

SELECT

MIN(a.ID)asmin_id

FROM

mlzm_contentasa

WHERE a.Data_Status=2GROUP BY

a.U_ID

)as b where b.min_id =c.ID

)

ORDER BY U_ID

耗时：0.0520 秒

小结：

in 和 exists 执行效率收 子表大小的影响，子表小in的效率高，反之，若子表大则exists的效率高。

in和exists效率比 join 低，因为只有1个字段判断，特别是在数据量大的时候差距更大，10万数据+的话 join 和 in至少差距在5分钟以上，因此最佳方式是使用INNER JOIN 连表查询。

//=====================================================================================================//

获取满足条件的最小一条数据或最大一条数据，sql优化后：

最小：

SELECT

c.*FROM

mlzm_content AS c

INNER JOIN (

SELECT

MIN(a.id)asmin_id

FROM a

WHERE a.status=2(条件判断)

GROUP BY a.U_ID (分组依据)

) AS b ON c.id=b.min_idwhere (其他条件)

最大：

SELECT

c.*FROM

mlzm_content AS c

INNER JOIN (

SELECT

max(a.id)asmax_id

FROM a

WHERE a.status=2(条件判断)

GROUP BY a.U_ID (分组依据)

) AS b ON c.id=b.max_idwhere (其他条件)

//=====================================================================================================//

上面利用group by分组方式 只能获取到最大或最小的，那么若是想要获取到指定位置的条数呢？如，获取满足条件的第5 条信息。

mysql中是不存在聚合函数 partition by的 ，要想实现类似功能需要利用 group_concat + substr等函数处理

partition by 语法

select .... over( partition by column1 order by column2)  from table_name ...

函数：

concat(str1, str2,...)

功能：将多个字符串连接成一个字符串

结果：返回的结果为参数相连接产生的字符串，注意，若其中任意参数为null,则返回的结果也为null

concat('str1','、 ','str2','、','str3')  对应的结果为：str1、str2、str3，但这样每次都要填写分隔符，看起来很是臃肿，那有没有简单的实现方式呢？

有那就是concat_ws(separator, str1, str2, ...)函数(concat_ws就是concat with separator)，功能与concat类似将多个字符串连接成一个字符串，但是可以一次性指定分隔符。

上面的代码可简化为concat_ws('、'，'str1', 'str2', 'str3')

注意：concat()和concat_ws()一样，只要传入的参数有null 则返回结果均为null,分隔符为null也是一样的。

group_concat(expr[表达式])聚合函数 ，

前言：在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。(有关group by的知识：浅析SQL中Group By的使用)。

group_concat()函数

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

SELECT

GROUP_CONCAT(a.ID)asids,a.U_ID

FROM

mlzm_content AS a

WHERE

a.Data_Status= 2GROUP BY

a.U_ID

ORDER BY a.U_ID

12957abc61ea932ce2ef1696388cea94.png

将id倒序连接

# 以'# #'来分割连接

SELECT

GROUP_CONCAT(DISTINCT a.ID ORDER BY a.id DESC separator'# #')asids,a.U_ID

FROM

mlzm_content AS a

WHERE

a.Data_Status= 2GROUP BY

a.U_ID

ORDER BY a.U_ID

01fb97ae269d43033a46c7c3bed6a2e2.png

接下来用substring_index(“待截取有用部分的字符串”，“截取数据依据的字符(分隔符)”，截取字符的位置N)函数来截取想要的数据

如：获取满足条件的前5 条信息

SELECT

substring_index(GROUP_CONCAT(DISTINCT a.ID ORDER BY a.id ASC separator','),',',5) assub_id,

a.U_ID

FROM

mlzm_content AS a

WHERE

a.Data_Status= 2GROUP BY

a.U_ID

ORDER BY a.U_ID

036bff90a3f18ea22b2484b37f10abd6.png

错误的获取(in)：

SELECT c.id,c.U_ID FROM mlzm_content asc

JOIN

(SELECT

substring_index(GROUP_CONCAT(DISTINCT a.ID ORDER BY a.id ASC separator','),',',5) assub_id,

a.u_id

FROM

mlzm_content AS a

WHERE

a.Data_Status= 2GROUP BY

a.U_ID

)ast

ON c.u_id=t.u_id

WHERE c.idin(t.sub_id)

ORDER BY c.u_id

407bd31ad547313c906969dba2381c4c.png

可以看出in操作只匹配了第一个元素。若想要用in 操作的话可把子查询的结果集提出来再当做参数传入，如用PHP 中拆成2步来进行，先通过substring_index()函数把满足条件的数据先筛选出来，再通过结果集去循环查询匹配的数据，但是这样效率低，占用资源多，(因此舍弃这种操作)

正确的获取(find_in_set):

SELECT c.id,c.u_id FROM mlzm_content asc

JOIN

(SELECT

substring_index(GROUP_CONCAT(DISTINCT a.ID ORDER BY a.id ASC separator','),',',5) assub_id,

a.u_id

FROM

mlzm_content AS a

WHERE

a.Data_Status= 2GROUP BY

a.U_ID

)ast

ON c.u_id=t.u_id

WHERE FIND_IN_SET(c.id,t.sub_id)

ORDER BY c.u_id

7bce2410e433c232c6778f9393e0cbf9.png

若是想得到前5条信息中最后一条信息

SELECT c.id,c.U_ID FROM mlzm_content asc

JOIN

(SELECT

substring_index(substring_index(GROUP_CONCAT(DISTINCT a.ID ORDER BY a.id ASC separator','),',',5),',',-1) assub_id,

a.U_ID

FROM

mlzm_content AS a

WHERE

a.Data_Status= 2GROUP BY

a.U_ID

)ast

ON c.id= t.sub_id

1dbdbc7a3b08a893f0bd410a74345f35.png

利用这个方案, 以下类似业务需求都可以这么做, 如:

1. 查找每个用户过去10个的登陆IP

2. 查找每个班级中总分最高的两个人

greatest(value1,value2,...)函数，获取传入参数中最大的值

SELECT greatest(1,2,3,4)

ea96b9f8f90a590fdbb01426ac641aaf.png

SELECT greatest('a','b','c','bb','ae','d')

ab92a573182844c98ce4b3723187aeed.png

SELECT greatest('a','b','c','bb','ae','d'),ASCII('a'),ASCII('b'),ASCII('c'),ASCII('bb'),ASCII('ae'),ASCII('d')

f795a0b5d8dd31231713a735539bdebc.png

从上面可以看出，greatest()函数在字符比较的时候，只取第一个字符进行ASCII 值比较

参考：https://blog.csdn.net/mary19920410/article/details/76545053/

in 与exists性能区分：https://www.cnblogs.com/beijingstruggle/p/5885137.html

mysql 函数参考：https://www.cnblogs.com/zwesy/p/9428509.html

你这人真狗
关注
mysql 每隔三天的分组_MYSQL每隔10分钟进行分组统计的实现方法
weixin_33493816的博客
01-27 803
前言本文的内容主要是介绍了MYSQL每隔10分钟进行分组统计的实现方法，在画用户登录、操作情况在一天内的分布图时会非常有用，之前我只知道用「存储过程」实现的方法(虽然执行速度快，但真的是太不灵活了)，后来学会了用高级点的「group by」方法来灵活实现类似功能。正文：-- time_str '2016-11-20 04:31:11'-- date_str 20161120select conca...
mysql分页分组查询_MySql多表查询分组查询,子查,分页查询
weixin_31993699的博客
01-19 661
一.多表查询查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置。SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.locFROM emp e,dept dWHERE e.deptno=d.deptno;1.全连接查询每个雇员的编号，姓名，职位，工资，部门名称，部门位置。SELECT e.empno,e.ename,e.job,e.sal,d.dname...
mysql group by 三天_Mysql第三天(高级查询、group by、having、子查询、单行(多行)运算符)...
weixin_33640562的博客
02-26 394
高级查询常用的分组函数COUNTMINMAXSUMAVG分组函数的语法:SELECT [column,] group_function(column)FROM table[WHERE condition][GROUP BY column][HAVING group_function(column)expression[ORDER BY column...
MySQL：GROUP BY 分组查询
最新发布
Zachyy的博客
07-31 1921
分组查询是SQL中一个非常强大的功能，它允许我们将数据按照一个或多个字段进行，并对每个分组进行（如求和、平均值、最大值、最小值等）。在MySQL中，我们使用GROUP BY关键字来实现分组查询。
mysql 时间段分组_MySQL查询按日期范围分组？
weixin_42524610的博客
01-18 790
I've got a table in MySQL that shows me number of hours logged on a daily basis. I'm trying to build a view that will allow me to quickly group my data by blocks/range of days. The simplest case wou...
根据不同日期类型分组查询 group by（日、周、月、季度、年）
Java小白白的博客
12-14 3454
根据不同日期类型分组查询 group by（日、周、月、季度、年）
mysql查询最近3天的数据
qq_50523945的博客
12-13 3431
下面就介绍一下如何用MySQL查询最近3天的数据。在MySQL中，可以使用NOW()函数获取当前时间，该函数返回一个DATETIME类型的值。接下来，我们需要获取3天前的时间。在MySQL中，可以使用DATE_SUB()函数来减去时间间隔。有了当前时间和3天前的时间，我们就可以使用BETWEEN关键字来查询时间范围内的数据了。amee是时间字段的名称。
mysql 返回map类型数据_mybatis group by查询返回map类型
weixin_39883462的博客
02-10 1886
故事的发生是这样的. . . . . . .一天 我发现我们的页面显示了这样的汇总统计数据，看起来体验还不错哦～～然后，我发现代码是这样滴：分开每个状态分别去查询数量。额e，可是为嘛不使用简单便捷的 group by 语句呢我们知道MyBatis提供了selectMap的方法，查询结果为hashmap。查询的时候，可以配置相应的传入参数和查询返回结果。对应dao 层代码如下：//查询各状态对应的数...
MySQL数据库配置文件中ONLY_FULL_GROUP_BY变量说明
txxjava5201314的博客
04-03 1288
MySQL的sql_mode合理设置 sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题. sql model 常用来解决下面几类问题 　　(1) 通过设置sql mode, 可以完成不同严格程度的...
mysql group by 汇总 优化_一次 group by + order by 性能优化分析
weixin_32512261的博客
01-19 449
工作了两三年，技术停滞不前，迷茫没有方向，不如看下我的直播 PHP 进阶之路 (金三银四跳槽必考，一般人我不告诉他)最近通过一个日志表做排行的时候发现特别卡，最后问题得到了解决，梳理一些索引和MySQL执行过程的经验，但是最后还是有5个谜题没解开，希望大家帮忙解答下主要包含如下知识点用数据说话证明慢日志的扫描行数到底是如何统计出来的从 group by 执行原理找出优化方案排序的实现细节gdb 源...
mysql指定天数分组
无关风月
08-20 1093
项目中有需求用到柱状图指定天数的分组（这里每三天分一组），因此花时间研究了一下，记录下来，实测有效，嘿嘿~ SELECT sum(k.tem_max),any_value(k.date_time) FROM ( select sum(统计字段) as 统计字段,DATE_FORMAT(date_time,'%Y-%m-%d') as date_time from 表名称as t where t.date_time >= '2021-03-02 00:00...
mysql 查询3天之内的数据
amanda_shuidi的博客
10-19 5668
SELECT * FROM Session where createTime >= now()-interval 3 day;  
mysql按照指定的天数对日期字段进行分组
weixin_42642782的博客
01-07 670
mysql按照指定的天数对日期字段进行分组 解决思想其实就是算出一个日期天数除天数得到的商进行分组 -- 28天月用电量均值 SELECT com_code, avgPower, month FROM (SELECT cons.com_code, AVG(acture_day_amount) avgPower, (DATEDIFF('2020-08-31', measure_date) DIV 28) AS `month`
对sorted函数的补充，以及与列表中的排序方法sort()的比较
qq_40709487的博客
02-28 309
sort方法： 　　1，sort()是对一个容器(列表)进行排序，无返回值，直接改变容器。 　　2，sort()能够运用key参数传入一个自定义的函数或者匿名函数(函数只能有一个参数)先对比较的元素进行某种操作，然后根据这个函数的返回值进行排序。 　　3，key的参数的函数只能是具有一个参数的函数。 内置sorted()函数： 　　1，sorted()可以对所有的可迭代对象进行排序，有返...
MySql分组后查询最新的数据
qq_44016171的博客
03-02 622
MySql分组后查询最新的数据
连续三天访问的用户情况-mysql
qq_33606071的博客
01-10 699
use ORG_DBRDS drop table if exists #aaa create table #aaa( dt date, userid int, isuse int ) -- 连续三天访问的userid，以及开始的时间 drop table if exists #continueVisit create table #continueVisit( userid int,dt date ,currentIs
partition by和group by对比
qq_44952352的博客
02-24 966
partition by和group by对比的一些个人理解 group by是分组函数，partition by是分析函数 直接举例 对表x中每个水果继续评分 name score 苹果 2 苹果 5 苹果 3 桃子 7 香蕉 1 香蕉 4 select name,score,sum(sore) from x group by name 是先分组之后，把每一组的sore相加，最后显示的是 name score 苹果 10 桃子
MySQL-50道经典sql题汇总
qq_41835813的博客
08-31 7655
建表 DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `CId` varchar(10) default NULL, `Cname` varchar(10) default NULL, `TId` varchar(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure fo.
sql 日期分组查询
asdl86的博客
03-31 1872
select GETDATE() 得到 2023-03-31 11:15:32.570。select GETDATE() 得到 2023-03-31 11:15:32.570。得到 2023-03-26 00:00:00.000 这应该可以。要的应该是整天吧，这出来 11点前的就不算了，不对头。从数据库查询最近N天的数量进行统计。这里还有个 datediff…小白不懂，好像还有问题。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值