联合查询分组取最新数据

最新推荐文章于 2022-11-19 10:00:17 发布
最新推荐文章于 2022-11-19 10:00:17 发布
阅读量322 收藏
点赞数
分类专栏： sql总结 文章标签： 数据库 sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/anyhowanywhere/article/details/126013278
版权

场景： sql分组取最大优化

描述

同事今天遇到一个查询，一次有问题，让我帮忙查看，先上数据，有两张浏览表需要取到用户最新的浏览值数据结构如下

表一

CREATE TABLE `ums_member_read202206`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `member_id` bigint(11) NOT NULL COMMENT '会员id',
  `product_id` bigint(11) NOT NULL COMMENT '商品id',
  `member_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员名称',
  `product_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `product_pic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',
  `product_sn` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品货号',
  `product_price_now` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品阅览价格',
  `create_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '阅览时间',
  `state` int(11) NULL DEFAULT 0 COMMENT '状态 0/未删除 1/已删除',
  `last_id` int(11) NOT NULL COMMENT '记录上次浏览id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '会员阅览记录表' ROW_FORMAT = Compact;

-- ----------------------------
-- Records of ums_member_read202206
-- ----------------------------
INSERT INTO `ums_member_read202206` VALUES (3, 17685, 6006, 'PaiTest123', 'Gnarhunters × Nike SB Dunk Low Pro Qs Black Black white', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220518/1652865848704.jpg?wh=750*600', 'DH7756-010', 1000.00, '2022-06-01 04:14:46', 0, 0);
INSERT INTO `ums_member_read202206` VALUES (4, 17685, 6026, 'PaiTest123', 'adidas Kids Yeezy Slide ONYX', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220523/1653293331836.jpg?wh=750*600', 'HQ4115', 0.00, '2022-06-26 05:21:29', 0, 3);
INSERT INTO `ums_member_read202206` VALUES (5, 17698, 2788, 'Li878906', 'adidas Yeezy Slide Pure (GW1934)', 'http://carryme-oss-prod.oss-accelerate.aliyuncs.com/mall/images/20211217/1639718432398.jpg?wh=750*600', 'GW1934', 10000.00, '2022-06-01 10:42:51', 0, 0);
INSERT INTO `ums_member_read202206` VALUES (6, 17698, 2788, 'Li878906', 'adidas Yeezy Slide Pure (GW1934)', 'http://carryme-oss-prod.oss-accelerate.aliyuncs.com/mall/images/20211217/1639718432398.jpg?wh=750*600', 'GW1934', 10000.00, '2022-06-26 11:42:51', 0, 0);
INSERT INTO `ums_member_read202206` VALUES (7, 17698, 6026, 'Li878906', 'adidas Kids Yeezy Slide ONYX', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220523/1653293331836.jpg?wh=750*600', 'HQ4115', 0.00, '2022-06-01 10:55:01', 0, 3);

表二、

CREATE TABLE `ums_member_read202207`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `member_id` bigint(11) NOT NULL COMMENT '会员id',
  `product_id` bigint(11) NOT NULL COMMENT '商品id',
  `member_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员名称',
  `product_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `product_pic` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片',
  `product_sn` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品货号',
  `product_price_now` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品阅览价格',
  `create_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '阅览时间',
  `state` int(11) NULL DEFAULT 0 COMMENT '状态 0/未删除 1/已删除',
  `last_id` int(11) NOT NULL COMMENT '记录上次浏览id',
  `last_id_state` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否为上月浏览ID（0：否 1：是）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 672 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '会员阅览记录表' ROW_FORMAT = Compact;

-- ----------------------------
-- Records of ums_member_read202207
-- ----------------------------
INSERT INTO `ums_member_read202207` VALUES (665, 17685, 6006, 'PaiTest123', 'Gnarhunters × Nike SB Dunk Low Pro Qs Black Black white', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220518/1652865848704.jpg?wh=750*600', 'DH7756-010', 1000.00, '2022-07-27 04:14:46', 0, 0, 0);
INSERT INTO `ums_member_read202207` VALUES (666, 17685, 6006, 'PaiTest123', 'Gnarhunters × Nike SB Dunk Low Pro Qs Black Black white', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220518/1652865848704.jpg?wh=750*600', 'DH7756-010', 1000.00, '2022-07-27 04:15:18', 0, 665, 0);
INSERT INTO `ums_member_read202207` VALUES (667, 17685, 6006, 'PaiTest123', 'Gnarhunters × Nike SB Dunk Low Pro Qs Black Black white', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220518/1652865848704.jpg?wh=750*600', 'DH7756-010', 1000.00, '2022-07-27 04:16:02', 0, 666, 0);
INSERT INTO `ums_member_read202207` VALUES (668, 17685, 6026, 'PaiTest123', 'adidas Kids Yeezy Slide ONYX', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220523/1653293331836.jpg?wh=750*600', 'HQ4115', 0.00, '2022-07-27 04:55:16', 0, 667, 0);
INSERT INTO `ums_member_read202207` VALUES (669, 17685, 6026, 'PaiTest123', 'adidas Kids Yeezy Slide ONYX', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220523/1653293331836.jpg?wh=750*600', 'HQ4115', 0.00, '2022-07-27 05:20:37', 0, 668, 0);
INSERT INTO `ums_member_read202207` VALUES (670, 17685, 6032, 'PaiTest123', 'Nike Air Jordan 1 Low Shadow Toe (GS)', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220524/1653354466056.jpg?wh=750*600', '553560-052', 0.00, '2022-07-27 11:55:14', 0, 0, 0);
INSERT INTO `ums_member_read202207` VALUES (671, 17685, 6032, 'PaiTest123', 'Nike Air Jordan 1 Low Shadow Toe (GS)', 'http://carryme-oss-prod.oss-ap-northeast-1.aliyuncs.com/mall/images/20220524/1653354466056.jpg?wh=750*600', '553560-052', 0.00, '2022-07-27 11:55:49', 0, 670, 0);

预想结果：

取到两张表里的最新的浏览量，获取如下 product_id,last_id,create
在这里插入图片描述
这里会有product_id 重复数据，同事的sql是这样写的

SELECT  DISTINCT
	union_read.product_id,
	union_read.create_time,
	union_read.last_id 
FROM
	(
	SELECT
		product_id,
		create_time,
		last_id 
	FROM
		ums_member_read202206 
	WHERE
		id IN ( SELECT MAX( id ) FROM ums_member_read202206 WHERE create_time >= '2022-06-26 00:00:00' AND create_time <= '2022-06-30 23:59:59' AND member_id = '17685' GROUP BY product_id ) UNION
	SELECT
		product_id,
		create_time,
		last_id 
	FROM
		ums_member_read202207 
	WHERE
		id IN ( SELECT MAX( id ) FROM ums_member_read202207 WHERE create_time >= '2022-07-01 00:00:00' AND create_time <= '2022-07-27 23:59:59' AND member_id = '17685' GROUP BY product_id ) 
	) AS union_read 
ORDER BY
	union_read.create_time DESC

原因分析和解决方案：

数据重复原因是应为 distinct 这个函数比较特殊 ，查询还必须放到首位，这样去重

select  distinct  字段一,字段二,字段三

而同事想要拿到 三个值，只对字段一去重，这样写就会影响字段二和字段三，将他两也放到去重里，是不符合期望数据 ，于是我就想既然这样 distinct 是可以配合聚合函数使用 (SQL Server支持，而Access不支持)

select  字段一,字段二,字段三,count(distinct  字段一) as 字段四

但发现有一个报错

In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ‘union_read.create_time’; this is incompatible with sql_mode=only_full_group_by
在这里插入图片描述
这是数据库版本引起的一个bug
由于太长跟本文也关系不大就放到最后 附录一
结果：
在这里插入图片描述

建议查询表查字段比较多，去重只有一个不要用distinct
换了个思路，换成GROUP BY
如下

	SELECT
	product_id,
	create_time,
	last_id,
	max(last_id)
FROM
	(
	SELECT
		product_id,
		create_time,
		last_id 
	FROM
		ums_member_read202206 
	WHERE
		id IN ( SELECT MAX( id ) FROM ums_member_read202206 WHERE create_time >= '2022-06-26 00:00:00' AND create_time <= '2022-06-30 23:59:59' AND member_id = '17685' GROUP BY product_id ) UNION
	SELECT
		product_id,
		create_time,
		last_id 
	FROM
		ums_member_read202207 
	WHERE
		id IN ( SELECT MAX( id ) FROM ums_member_read202207 WHERE create_time >= '2022-07-01 00:00:00' AND create_time <= '2022-07-27 23:59:59' AND member_id = '17685' GROUP BY product_id ) 
	) AS union_read 
WHERE
	create_time >= '2022-06-28 00:00:00' 
	AND create_time <= '2022-07-27 10:04:21' 
GROUP BY
	product_id
ORDER BY
	max(last_id) desc

创作不易，留个赞吧!

附录一(版本bug)

1：命令行中输入：

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

不过这种情况治标不治本，一旦mysql重启之后又会恢复。

2：修改MySQL的配置文件，

1、windows下找到MySQL的安装目录的my.ini文件，修改其中的配置为不启动ONLY_FULL_GROUP_BY模式

删掉带有ONLY_FULL_GROUP_BY的模式就ok了，如果没有找到my.ini文件。

去系统的隐藏文件夹查看，在某个盘下输入%ProgramData%然后搜索MySQL的my.ini文件

2、linux下找到my.cnf文件，这个是配置MySQL的文件。一般这个文件是在etc文件夹下。

vi my.cnf 编辑这个文件，然后在图示的位置上加入

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

然后重启MySQL服务：service mysqld restart

小*-^-*九
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL 多表关联一对多查询实现取最新一条数据的方法示例
09-08
主要介绍了MySQL 多表关联一对多查询实现取最新一条数据的方法,结合实例形式详细分析了MySQL 多表关联一对多查询实现取最新一条数据相关原理、实现方法与操作注意事项,需要的朋友可以参考下
SQL Server 多表联合查询取最新一条数据
分享经验，共同进步.
06-07 6625
所有的SQL语句如下： create database David; use David --雨量站表 create table RainSite ( ID int not null primary key identity(1,1), SiteCode varchar(8) not null, SiteName varchar(20) not null, Latitut
HQL实现查询数据库最新数据（id自增）
penglu123456的专栏
08-08 1057
使用语句：select  max(bean.id) from ActivityPhoto bean (绿色表示需要查询数据表对应的实体类名)。此语句可以查到最大id，而不是一条记录。 故需要使用嵌套语句查询：select ap from ActivityPhoto as ap where ap.id = (select  max(bean.id) from ActivityPhoto bea
HQL取满足条件最新一条记录（max()函数的使用）
fengj_j的专栏
04-29 1万+
项目中要查询满足条件的最新一条记录，用的是Hibernate框架，因此要使用HQL语句： from DeviceUseRecord where DeviceUseRecord.id =(select max(DeviceUseRecord.id) from DeviceUseRecord where DeviceUseRecord.deviceNo='xxxxxx')"; 结果执行时出错，网上
HQL查询
笑一个吧
03-07 642
HQL 1，HQL描写对象操作的一种查询语言 Hibernate特有 2，HQL的语法与SQL基本一致 不同的是HQL是面向对象的查询 查询的是对象和对象中的属性 3，HQL的关键字不区分大小写 但类名和属性区分大小写 select 别名/属性名/表达式 from 实体 as 别名 where 过滤条件 group by 分组条件 having 分组后的过滤条件 order by 排序条件 查询所有用户 @Test public void method(){ /**
mysql连接查询、联合查询、子查询原理与用法实例详解
09-08
MySQL中的查询操作是数据库操作的核心，它包括连接查询、联合查询和子查询，这些方法都是在处理多表数据时非常重要的技术。 连接查询是将两个或更多表的数据结合在一起进行查询，主要有四种类型：内连接（INNER ...
cakephp2.X多表联合查询join及使用分页查询的方法
10-20
在CakePHP 2.x框架中，进行多表联合查询（JOIN）和分页查询是数据库操作中的常见需求，尤其在处理复杂数据时显得尤为重要。以下将详细解释如何在CakePHP中实现这些功能。 首先，多表联合查询是通过JOIN语句来完成的...
数据分析系列 16/32 | MySQL中子查询与联合查询
01-19
前面说了很多MySQL中的查询，比如条件查询、分组聚合查询、连接查询，今天来说一下另外两个非常的重要的查询，MySQL中的子查询和联合查询。 PART01  子查询 子查询也称嵌套查询，是将一个查询语句嵌套在另一个查询...
【一文看懂SQL系列】分页查询，联合查询 精辟总结
12-14
应用场景：当要显示的数据，一页显示不全，需要分页提交SQL请求 语法： 执行顺序从1-9 SELECT 查询列表 7 FROM 表名 1 JOIN 表2 2 ON 连接条件 3 WHERE 筛选条件 4 GROUP BY 分组条件 5 HAVING 分组后筛选 6 ...
MySQL 使用DQL命令查询数据的实现方法
12-16
本篇文章博主将带大家一起学习MySQL中常用的数据查询语言。 DQL(Data Query Language 数据查询语言) ...[left | out | inner join table_name2] #联合查询 [WHERE ...] #指定结果需要满足的条件 [GROUP BY ...] #指定结
MySQL实现分组查每组最新创建的一条数据
weixin_45535408的博客
11-19 1276
因为使用了group by分组关键字，所以无法直接查某个字段的数据，需要结合函数才能查询特定字段出来。若需要查询其他字段的数据，需要与自身表进行联表查询。使用max函数结合分组关键字实现。取每个分组下最新创建的一条数据。MySQL5.7.5之后版本。
hql取满足条件最新一条记录_一条 SQL 统计大V涨粉排行榜
weixin_39552204的博客
11-22 131
古时的风筝第70篇原创文章今天来说一个最近遇到的小需求，不是什么高大上的需求，当然也就谈不上什么技术深度。其实，大多数时候，我们的日常开发就是由这些不起眼的小需求组成，而且今天要说的其实就是纯用一条 SQL 实现的，而这类 SQL 还是比较典型的。需求说明是这样的，有一个某站的数据抓取程序(当然是合法抓取啦)，每天会抓取一次固定的几百个头部大V 的主要信息，主要就是粉丝数量，存储...
Excel VLOOKUP实用教程之 02 vlookup如何双向查找，两个字段查询数据？（教程含数据excel）
iCloudEnd的博客
09-08 1684
VLOOKUP 函数是基准。如果您知道如何使用 VLOOKUP 函数，您就知道 Excel 中的一些内容。如果你不这样做，你最好不要在简历中将 Excel 列为你的强项之一。我参加了小组面试，一旦候选人提到 Excel 作为他的专业领域，首先被问到的是 “你明白么 VLOOKUP 功能？既然我们知道了这个 Excel 函数的重要性，那么完全掌握它以便能够自豪地说——“我在 Excel 中知道一两件事”是有道理的。这将是一个庞大的 VLOOKUP 教程（按照我的标准）。
主流SQL查询多条只取其中最新的一条数据
热门推荐
Cx的博客
11-12 1万+
业务场景： 在针对某个类型数据存在多条时，但只想取最新的一条。在可以确定时哪种类型时我们使用简单sql就可以解决。 ORACLE-SQL： 如: select * from ( select * from t_table a where a.tpye=? order by a.time desc ) where rownum=1; MYSQL-SQL: 如： select * from t_table a where a.type=? ...
hql先排序后分组（查询分组中的最新）（hql 执行原生态sql）
学而不思则罔 思而不学则殆
01-13 5118
如果用这种方法进行查询： String queryString = "select * from (select * from resource r order by r.createTime desc) t group by t.resId"; Query query = entityManager.createQuery(queryString); 会报错，因为Hibernate不支持这种
hql查询数据库表中最新一条数据
weixin_33831673的博客
01-12 1350
2019独角兽企业重金招聘Python工程师标准>>> ...
hql语句查询当日数据
liaoyanshuo的博客
06-08 2790
    @Query("SELECT sum(u.jindudianshu) From Tkf_mrrwjd u WHERE u.guanjiaId =?1 AND DateDiff(u.date,NOW())=0")    Integer findPointByWaiterId(Integer guanjiaId);
mysql表之联合查询
最新发布
09-23
在MySQL中，多表联合查询可以使用交叉连接、内连接、外连接、分组查询和子查询等五种方式。交叉连接(CROSS JOIN)是指两个表进行连接，返回的结果是两个表的笛卡尔积。内连接是指根据两个表之间的关联条件，返回满足条件的数据。外连接可以分为左外连接和右外连接，左外连接返回左表的所有数据和满足关联条件的右表数据，右外连接返回右表的所有数据和满足关联条件的左表数据。分组查询是指对查询结果进行分组，然后进行聚合操作。子查询是指在一个查询中嵌套了另一个查询。 在MySQL中，可以使用以下语句进行多表联合查询： 1. 交叉连接(CROSS JOIN)： SELECT * FROM table1 CROSS JOIN table2; 2. 内连接： SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; 3. 左外连接： SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; 4. 右外连接： SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; 5. 分组查询： SELECT column1, COUNT(column2) FROM table GROUP BY column1; 6. 子查询： SELECT column1 FROM table1 WHERE column2 IN (SELECT column3 FROM table2);

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

小*-^-*九

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

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

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

打赏作者

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

抵扣说明：

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

余额充值