记录一次MySQL查询优化

最新推荐文章于 2023-10-11 15:47:52 发布
最新推荐文章于 2023-10-11 15:47:52 发布
阅读量138 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jesson_c/article/details/120081773
版权 
WITH `tmp` AS (
	SELECT
		`m`.`FactoryName` AS `FactoryName`,
		`m`.`Spec` AS `Spec`,
		`m`.`MaterialType` AS `MaterialType`,
		`m1`.`ProductionPlanID` AS `ProductionPlanID`,
		`m`.`PurchCycle` AS `PurchCycle`,
		`m`.`MaterialCheckCycle` AS `MaterialCheckCycle`,
		`m`.`WhSumQty` AS `WhSumQty`,
		ifnull( `m`.`SafetyStockQty`, 0 ) AS `SafetyStockQty`,
	IF
		(((((
							ifnull( `m`.`WhSumQty`, 0 ) + ifnull( `m`.`NextArrivalQty`, 0 )) - ifnull( `m`.`ConsumeQty`, 0 )) - ifnull( `m`.`SafetyStockQty`, 0 )) > 0 
				),
			1,
			0 
			) AS `SafeStatus`,(((
				ifnull( `m`.`WhSumQty`, 0 ) + ifnull( `m`.`NextArrivalQty`, 0 )) - ifnull( `m`.`ConsumeQty`, 0 )) - ifnull( `m`.`SafetyStockQty`, 0 )) AS `RemainQty` 
	FROM
		(
			`mm_mrp_dt` `m`
			LEFT JOIN `mm_mrp_main` `m1` ON ((
					`m1`.`MMRPID` = `m`.`MMRPID` 
				))) 
	WHERE
		(
		`m`.`MaterialType` IN ( 1, 2 ))),
	`tmp1` AS (
	SELECT
		`m`.`ProcessID` AS `ProcessID`,
		`m1`.`ScheduleDate` AS `ScheduleDate`,
		`m`.`VendorName` AS `VendorName`,
		`m`.`Spec` AS `Spec`,
		`m2`.`ProductionPlanID` AS `ProductionPlanID`,(
		CASE
				`m`.`ProcessID` 
				WHEN '7F3596F606336B5D' THEN
				'前驱体' ELSE '锂盐' 
			END 
			) AS `Type`,
			sum( `m`.`InspectVal` ) AS `DailyQty`,(
			CASE
					
					WHEN ( `tmp`.`SafeStatus` = 2 ) THEN
					0 ELSE (
					SELECT
						( sum(( CASE WHEN ( `main`.`BillType` = '628' ) THEN -( `dt`.`Qty` ) ELSE `dt`.`Qty` END )) / 1000 ) AS `Qty` 
					FROM
						(((
									`wm_instockbill_dt` `dt`
									LEFT JOIN `wm_instockbill_main` `main` ON ((
											`main`.`InStockBillID` = `dt`.`InStockBillID` 
										)))
								LEFT JOIN `wm_batch` `b` ON ((
										`b`.`BatchID` = `dt`.`BatchID` 
									)))
							LEFT JOIN `bd_vendor` `bd` ON ((
									`b`.`PartnerID` = `bd`.`VendorID` 
								))) 
					WHERE
						((
								`main`.`BillType` IN ( '611', '628' )) 
							AND ( `main`.`Status` = '2' ) 
							AND (
							`main`.`InStockDate` = ( `m1`.`ScheduleDate` - INTERVAL ( `tmp`.`PurchCycle` + `tmp`.`MaterialCheckCycle` ) DAY )) 
							AND ( `dt`.`MaterialID` = `m`.`Spec` ) 
						AND ( `b`.`PartnerID` = `m`.`VendorName` )) 
					GROUP BY
						`main`.`InStockDate`,
						`dt`.`MaterialID`,
						`b`.`PartnerID` 
					) 
				END 
				) AS `ActualQty`,
				`tmp`.`WhSumQty` AS `WhSumQty`,
				`tmp`.`RemainQty` AS `RemainQty`,
				`tmp`.`SafetyStockQty` AS `SafetyStockQty`,
				`tmp`.`PurchCycle` AS `PurchCycle`,
				`tmp`.`MaterialCheckCycle` AS `MaterialCheckCycle` 
			FROM
				(((
							`mm_imitate_schedule_material` `m`
							LEFT JOIN `mm_imitate_schedule_dt` `m1` ON ((
									`m1`.`ScheduleDtID` = `m`.`ScheduleDtID` 
								)))
						LEFT JOIN `mm_imitate_schedule_main` `m2` ON ((
								`m2`.`ScheduleID` = `m`.`ScheduleID` 
							)))
					LEFT JOIN `tmp` ON (((
								`m`.`VendorName` = `tmp`.`FactoryName` 
								) 
							AND ( `tmp`.`MaterialType` = 1 ) 
							AND ( `m`.`Spec` = `tmp`.`Spec` ) 
						AND ( `tmp`.`ProductionPlanID` = `m2`.`ProductionPlanID` )))) 
			WHERE
				( `m`.`ProcessID` = '7F3596F606336B5D' ) 
			GROUP BY
				`m`.`ProcessID`,
				`m1`.`ScheduleDate`,
				`m`.`VendorName`,
				`m`.`Spec`,
				`m2`.`ProductionPlanID` 
			),
			`tmp2` AS (
			SELECT
				`tmp1`.`ProcessID` AS `ProcessID`,(
					`tmp1`.`ScheduleDate` - INTERVAL ( `tmp1`.`PurchCycle` + `tmp1`.`MaterialCheckCycle` ) DAY 
					) AS `ScheduleDate`,(
				SELECT
					group_concat( `bd_vendor`.`VendorName` ORDER BY find_in_set( `bd_vendor`.`VendorID`, `tmp1`.`VendorName` ) ASC SEPARATOR ',' ) 
				FROM
					`bd_vendor` 
				WHERE
					(
					0 <> find_in_set( `bd_vendor`.`VendorID`, `tmp1`.`VendorName` ))) AS `VendorName`,
				`tmp1`.`VendorName` AS `VendorID`,
				`tmp1`.`Spec` AS `Spec`,(
				SELECT
					group_concat( `bd_material`.`MaterialName` ORDER BY find_in_set( `bd_material`.`MaterialID`, `tmp1`.`Spec` ) ASC SEPARATOR ',' ) 
				FROM
					`bd_material` 
				WHERE
					(
					0 <> find_in_set( `bd_material`.`MaterialID`, `tmp1`.`Spec` ))) AS `MaterialName`,
				`tmp1`.`Type` AS `Type`,
				`tmp1`.`DailyQty` AS `DailyQty`,
				`tmp1`.`RemainQty` AS `RemainQty`,
				`tmp1`.`ScheduleDate` AS `ScheduleDate_temp`,
				`tmp1`.`WhSumQty` AS `WhSumQty`,
				`tmp1`.`SafetyStockQty` AS `SafetyStockQty`,
				round( `tmp1`.`ActualQty`, 3 ) AS `ActualQty`,
				concat_ws(
					'_',
					`tmp1`.`ProcessID`,
					`tmp1`.`VendorName`,
					`tmp1`.`Spec`,
				CONVERT ( `tmp1`.`Type` USING utf8 )) AS `UniqeID` 
			FROM
				`tmp1` 
			),
			`tmp11` AS (
			SELECT
				`m`.`ProcessID` AS `ProcessID`,
				`m1`.`ScheduleDate` AS `ScheduleDate`,
				`m`.`VendorName` AS `VendorName`,
				`m`.`Spec` AS `Spec`,
				`m2`.`ProductionPlanID` AS `ProductionPlanID`,(
				CASE
						`m`.`ProcessID` 
						WHEN '7F3596F606336B5D' THEN
						'前驱体' ELSE '锂盐' 
					END 
					) AS `Type`,
					sum( `m`.`InspectVal` ) AS `DailyQty`,(
					CASE
							
							WHEN ( `tmp`.`SafeStatus` = 1 ) THEN
							0 ELSE (
							SELECT
								( sum(( CASE WHEN ( `main`.`BillType` = '628' ) THEN -( `dt`.`Qty` ) ELSE `dt`.`Qty` END )) / 1000 ) AS `Qty` 
							FROM
								(((
											`wm_instockbill_dt` `dt`
											LEFT JOIN `wm_instockbill_main` `main` ON ((
													`main`.`InStockBillID` = `dt`.`InStockBillID` 
												)))
										LEFT JOIN `wm_batch` `b` ON ((
												`b`.`BatchID` = `dt`.`BatchID` 
											)))
									LEFT JOIN `bd_vendor` `bd` ON ((
											`b`.`PartnerID` = `bd`.`VendorID` 
										))) 
							WHERE
								((
										`main`.`BillType` IN ( '611', '628' )) 
									AND ( `main`.`Status` = '2' ) 
									AND (
									`main`.`InStockDate` = ( `m1`.`ScheduleDate` - INTERVAL ( `tmp`.`PurchCycle` + `tmp`.`MaterialCheckCycle` ) DAY )) 
									AND ( `dt`.`MaterialID` = `m`.`Spec` ) 
								AND ( `b`.`PartnerID` = `m`.`VendorName` )) 
							GROUP BY
								`main`.`InStockDate`,
								`dt`.`MaterialID`,
								`b`.`PartnerID` 
							) 
						END 
						) AS `ActualQty`,
						`tmp`.`WhSumQty` AS `WhSumQty`,
						`tmp`.`RemainQty` AS `RemainQty`,
						`tmp`.`SafetyStockQty` AS `SafetyStockQty`,
						`tmp`.`PurchCycle` AS `PurchCycle`,
						`tmp`.`MaterialCheckCycle` AS `MaterialCheckCycle` 
					FROM
						(((
									`mm_imitate_schedule_material` `m`
									LEFT JOIN `mm_imitate_schedule_dt` `m1` ON ((
											`m1`.`ScheduleDtID` = `m`.`ScheduleDtID` 
										)))
								LEFT JOIN `mm_imitate_schedule_main` `m2` ON ((
										`m2`.`ScheduleID` = `m`.`ScheduleID` 
									)))
							LEFT JOIN `tmp` ON (((
										`m`.`VendorName` = `tmp`.`FactoryName` 
										) 
									AND ( `tmp`.`MaterialType` = 2 ) 
									AND ( `m`.`Spec` = `tmp`.`Spec` ) 
								AND ( `tmp`.`ProductionPlanID` = `m2`.`ProductionPlanID` )))) 
					WHERE
						( `m`.`ProcessID` = 'BBECC27125E56409' ) 
					GROUP BY
						`m`.`ProcessID`,
						`m1`.`ScheduleDate`,
						`m`.`VendorName`,
						`m`.`Spec`,
						`m2`.`ProductionPlanID` 
					),
					`tmp12` AS (
					SELECT
						`tmp11`.`ProcessID` AS `ProcessID`,(
							`tmp11`.`ScheduleDate` - INTERVAL ( `tmp11`.`PurchCycle` + `tmp11`.`MaterialCheckCycle` ) DAY 
							) AS `ScheduleDate`,(
						SELECT
							group_concat( `bd_vendor`.`VendorName` ORDER BY find_in_set( `bd_vendor`.`VendorID`, `tmp11`.`VendorName` ) ASC SEPARATOR ',' ) 
						FROM
							`bd_vendor` 
						WHERE
							(
							0 <> find_in_set( `bd_vendor`.`VendorID`, `tmp11`.`VendorName` ))) AS `VendorName`,
						`tmp11`.`VendorName` AS `VendorID`,
						`tmp11`.`Spec` AS `Spec`,(
						SELECT
							group_concat( `bd_material`.`MaterialName` ORDER BY find_in_set( `bd_material`.`MaterialID`, `tmp11`.`Spec` ) ASC SEPARATOR ',' ) 
						FROM
							`bd_material` 
						WHERE
							(
							0 <> find_in_set( `bd_material`.`MaterialID`, `tmp11`.`Spec` ))) AS `MaterialName`,
						`tmp11`.`Type` AS `Type`,
						`tmp11`.`DailyQty` AS `DailyQty`,
						`tmp11`.`RemainQty` AS `RemainQty`,
						`tmp11`.`ScheduleDate` AS `ScheduleDate_temp`,
						`tmp11`.`WhSumQty` AS `WhSumQty`,
						`tmp11`.`SafetyStockQty` AS `SafetyStockQty`,
						round( `tmp11`.`ActualQty`, 3 ) AS `ActualQty`,
						concat_ws(
							'_',
							`tmp11`.`ProcessID`,
							`tmp11`.`VendorName`,
							`tmp11`.`Spec`,
						CONVERT ( `tmp11`.`Type` USING utf8 )) AS `UniqeID` 
					FROM
						`tmp11` 
					),
					`tmpzh` AS (
					SELECT
						`tmp2`.`ProcessID` AS `ProcessID`,
						`tmp2`.`ScheduleDate` AS `ScheduleDate`,
						`tmp2`.`VendorName` AS `VendorName`,
						`tmp2`.`VendorID` AS `VendorID`,
						`tmp2`.`Spec` AS `Spec`,
						`tmp2`.`MaterialName` AS `MaterialName`,
						`tmp2`.`Type` AS `Type`,
						`tmp2`.`DailyQty` AS `DailyQty`,
						`tmp2`.`RemainQty` AS `RemainQty`,
						`tmp2`.`ScheduleDate_temp` AS `ScheduleDate_temp`,
						`tmp2`.`WhSumQty` AS `WhSumQty`,
						`tmp2`.`SafetyStockQty` AS `SafetyStockQty`,
						`tmp2`.`ActualQty` AS `ActualQty`,
						`tmp2`.`UniqeID` AS `UniqeID` 
					FROM
						`tmp2` UNION ALL
					SELECT
						`tmp12`.`ProcessID` AS `ProcessID`,
						`tmp12`.`ScheduleDate` AS `ScheduleDate`,
						`tmp12`.`VendorName` AS `VendorName`,
						`tmp12`.`VendorID` AS `VendorID`,
						`tmp12`.`Spec` AS `Spec`,
						`tmp12`.`MaterialName` AS `MaterialName`,
						`tmp12`.`Type` AS `Type`,
						`tmp12`.`DailyQty` AS `DailyQty`,
						`tmp12`.`RemainQty` AS `RemainQty`,
						`tmp12`.`ScheduleDate_temp` AS `ScheduleDate_temp`,
						`tmp12`.`WhSumQty` AS `WhSumQty`,
						`tmp12`.`SafetyStockQty` AS `SafetyStockQty`,
						`tmp12`.`ActualQty` AS `ActualQty`,
						`tmp12`.`UniqeID` AS `UniqeID` 
					FROM
						`tmp12` 
					),
					`tmp3` AS (
					SELECT
						`tmpzh`.`ProcessID` AS `ProcessID`,
						`tmpzh`.`ScheduleDate` AS `ScheduleDate`,
						`tmpzh`.`VendorName` AS `VendorName`,
						`tmpzh`.`VendorID` AS `VendorID`,
						`tmpzh`.`Spec` AS `Spec`,
						`tmpzh`.`MaterialName` AS `MaterialName`,
						`tmpzh`.`Type` AS `Type`,
						`tmpzh`.`DailyQty` AS `DailyQty`,
						`tmpzh`.`RemainQty` AS `RemainQty`,
						`tmpzh`.`ScheduleDate_temp` AS `ScheduleDate_temp`,
						`tmpzh`.`WhSumQty` AS `WhSumQty`,
						`tmpzh`.`SafetyStockQty` AS `SafetyStockQty`,
						`tmpzh`.`ActualQty` AS `ActualQty`,
						`tmpzh`.`UniqeID` AS `UniqeID`,
						sum( `tmpzh`.`DailyQty` ) OVER ( PARTITION BY `tmpzh`.`UniqeID` ORDER BY `tmpzh`.`ProcessID`, `tmpzh`.`ScheduleDate`, `tmpzh`.`VendorID`, `tmpzh`.`Spec` ) AS `PreTotal`,
						sum( `tmpzh`.`ActualQty` ) OVER ( PARTITION BY `tmpzh`.`UniqeID` ORDER BY `tmpzh`.`ProcessID`, `tmpzh`.`ScheduleDate`, `tmpzh`.`VendorID`, `tmpzh`.`Spec` ) AS `PreOutTotal` 
					FROM
						`tmpzh` 
					),
					`tmp4` AS (
					SELECT
						`tmp3`.`ProcessID` AS `ProcessID`,
						`tmp3`.`ScheduleDate` AS `ScheduleDate`,
						`tmp3`.`VendorName` AS `VendorName`,
						`tmp3`.`MaterialName` AS `MaterialName`,
						`tmp3`.`Type` AS `Type`,
						`tmp3`.`VendorID` AS `VendorID`,
						`tmp3`.`Spec` AS `Spec`,
						`tmp3`.`WhSumQty` AS `WhSumQty`,
						`tmp3`.`SafetyStockQty` AS `SafetyStockQty`,
						`tmp3`.`DailyQty` AS `DailyQty`,
						`tmp3`.`PreTotal` AS `PreTotal`,
						`tmp3`.`RemainQty` AS `RemainQty`,
						`tmp3`.`UniqeID` AS `UniqeID`,(
						CASE
								
								WHEN ( `tmp3`.`PreTotal` <= `tmp3`.`RemainQty` ) THEN
								0 
								WHEN ((
										`tmp3`.`PreTotal` > `tmp3`.`RemainQty` 
										) 
									AND (( `tmp3`.`DailyQty` - ( `tmp3`.`PreTotal` - `tmp3`.`RemainQty` )) > 0 )) THEN
									(
									`tmp3`.`DailyQty` - ( `tmp3`.`PreTotal` - `tmp3`.`RemainQty` )) ELSE `tmp3`.`DailyQty` 
								END 
								) AS `PlanQty`,
								sum((
									CASE
											
											WHEN ( `tmp3`.`PreTotal` <= `tmp3`.`RemainQty` ) THEN
											0 
											WHEN ((
													`tmp3`.`PreTotal` > `tmp3`.`RemainQty` 
													) 
												AND (( `tmp3`.`DailyQty` - ( `tmp3`.`PreTotal` - `tmp3`.`RemainQty` )) > 0 )) THEN
												(
												`tmp3`.`DailyQty` - ( `tmp3`.`PreTotal` - `tmp3`.`RemainQty` )) ELSE `tmp3`.`DailyQty` 
											END 
											)) OVER ( PARTITION BY `tmp3`.`UniqeID` ORDER BY `tmp3`.`Type`, `tmp3`.`ProcessID`, `tmp3`.`ScheduleDate`, `tmp3`.`VendorID`, `tmp3`.`Spec` ) AS `PrePlanQty`,(
										CASE
												
												WHEN ( `tmp3`.`ActualQty` IS NULL ) THEN
												0 ELSE `tmp3`.`ActualQty` 
											END 
												) AS `ActualQty`,(
											CASE
													
													WHEN ( `tmp3`.`PreOutTotal` IS NULL ) THEN
													0 ELSE `tmp3`.`PreOutTotal` 
												END 
												) AS `PreOutTotal` 
											FROM
												`tmp3` 
											) SELECT
											`tmp4`.`VendorID` AS `VendorID`,
											`tmp4`.`ProcessID` AS `ProcessID`,
											`tmp4`.`ScheduleDate` AS `ScheduleDate`,
											`tmp4`.`VendorName` AS `VendorName`,
											`tmp4`.`MaterialName` AS `MaterialName`,
											`tmp4`.`Type` AS `Type`,
											`tmp4`.`PlanQty` AS `PlanQty`,
											`tmp4`.`ActualQty` AS `ActualQty`,
											`tmp4`.`WhSumQty` AS `WhSumQty`,
											`tmp4`.`SafetyStockQty` AS `SafetyStockQty`,
											`tmp4`.`PrePlanQty` AS `PrePlanQty`,
											`tmp4`.`PreOutTotal` AS `PreOutTotal`,(((
														`tmp4`.`WhSumQty` - `tmp4`.`SafetyStockQty` 
														) + `tmp4`.`PrePlanQty` 
													) - `tmp4`.`PreOutTotal` 
											) AS `gap`,
											`tmp4`.`UniqeID` AS `UniqeID`,
											concat( '当天库存:', `tmp4`.`WhSumQty`, ';\n安全库存:', `tmp4`.`SafetyStockQty`, ';\n累计计划入库:', `tmp4`.`PrePlanQty`, ';\n累计实际入库:', `tmp4`.`PreOutTotal` ) AS `info` 
									FROM
	`tmp4`

1.首先，这个sql长这样，嗯。。。又臭又长，优化的话我一开始还是准备从索引开始(因为这个sql太复杂了，改起来脑壳疼)，先看下执行计划，查询用时6.9s。

 这个表wm_instockbill_main全表扫描了，而且有17100条，这个得优化，看sql里面有三个查询条件，这样可以加个联合索引（BillType, Status, InStockDate）。

 再看下执行计划，已经走索引了，而且过滤907，看下查询时间2.339s,嗯。。已经快了一点。

 继续优化，上面执行计划还有俩个表全表扫描了行数比较多，分别是bd_material,bd_vendor。看下sql里面，vendorid是主键，因该是会走索引的，这里失效了，难道是find_in_set导致的？换掉试下。

 改成这样

再看下执行计划，走索引了，速度也达到0.5s,还行吧。 

 

jesson_c
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql查询最近一条记录的sql语句(优化篇)
09-09
在MySQL数据库中，查询最近一条记录通常...总结来说，优化查询最近一条记录的SQL语句，关键在于减少全表扫描，利用索引以及聚合函数来提高查询效率。在处理大量数据时，应优先考虑效率更高的方法，以降低数据库的负载。
mysql 分组获取最新一条记录
WWF_HelloWorld
03-21 6552
1. 需求 按用户名分组，获取最新插入的一条记录。 2. 模拟数据 -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL...
Mysql分组查询每组最新的一条数据，查询用户的最新的一条记录
最新发布
曾沂宏的博客
10-11 706
如果直接使用group by 去获取数据，获取到的是最旧的一条数据。有订单表和用户表，如何获取用户的最新的一条订单记录？可以搭配max函数来实现。
mysql 查询时间最大（最新）的一条记录的正确姿势【经验证有效】
07-12 3万+
对于 MySQL查询时间最大或最新的一条记录的SQL写法，在网上找了好多，大部分都是错的，这些错的都还基本都一样的写法，可能也都是抄别人的，都没有认真去试验过，不负责的表现，最后找到一篇，经测试后成功查出最新的一条记录。 对于网上的"mysql查询最近一条记录"问题解决方案，什么还提供下策、中策、下策什么鬼的，根本就不行，不仅不行还是错的SQL，网上大部分的max()的写法都是只是查出了最大的那...
MySQL分组查询最新的一条记录
zzztimes的博客
12-13 2157
其实不然，这样做是为了将子查询的排序生效，这个时候添加order by语句，可以试试，是不是成功的在子查询里进行排序了？其实不然，这样做是为了将子查询的排序生效，这个时候添加order by语句，可以试试，是不是成功的在子查询里进行排序了？：首先分组排序功能在表中的体现一定是两个字段，分组字段(例如用户id，菜单code)和排序字段(创建时间，更新时间)。：首先分组排序功能在表中的体现一定是两个字段，分组字段(例如用户id，菜单code)和排序字段(创建时间，更新时间)。分组时字段谁先谁后无影响。
mysql获取数据中最新的一条
m0_62446245的博客
10-27 8723
select * from ( select t.* row_number() over(partition by t.prci_id order by t.date_time desc) as rowNum from t_user t where t.userId ='' --用户的id 有其他条件可以在这加 ) a where a.rowNum = 1
一次Mysql使用IN大数据量的优化记录
01-19
mysql版本号是5.7.28，表A有390W条记录，使用InnoDB引擎，其中varchar类型字段mac已建立索引，索引方法为B-tree。B表仅有5000+条记录。 有一条SQL指令是这样写的： SELECT * FROM A WHERE mac IN(aa:aa:aa:aa:aa:aa...
mysql关联子查询的一种优化方法分析
09-10
在数据库查询优化中，关联子查询是一种常见的操作，但它的性能往往不如预期。当涉及到大型数据表时，关联子查询可能导致显著的延迟。本文将深入探讨MySQL关联子查询的优化方法，通过实例来展示如何改进查询效率。 ...
一次MySQL慢查询导致的故障
12-15
我们知道分析MySQL语句查询性能的方法除了使用EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。 一、 起因 研发反应某台数据库僵死，后面的...
mysql分组并查询最新记录信息(亲测有效)
weixin_43401380的博客
02-23 8304
mysql分组并查询最新记录信息(亲测有效)
Mysql 获取分组最新一条记录SQL
_binlong的博客
02-16 5275
mysql取分组后最新的一条记录, 先筛选 出最大和最新的时间,在连表查询 就是最新的一条数据了。
Mysql分组查询每组最新的一条数据（五种实现方法）
热门推荐
kerwin的博客
12-06 7万+
在写报表功能时遇到一个需要根据用户id分组查询最新一条钱包明细数据的需求，在写sql测试时遇到一个有趣的问题，开始使用子查询根据时间倒序+group by customer_id发现查询出来的数据一直都是最旧的一条，而不是我需要的最新一条数据我明明已经倒序排了，后来总结出了四种解决方案，可以根据业务需求选择方案
Mysql查询最后一条（最新）数据
a928108554的博客
08-04 2万+
查询mysql数据库中最后一条(最新添加)的数据
关于mysql 从一张表查询出每个ID最新的记录的方式
LLLLLer的博客
07-02 4255
问题描述：在开发过程中经常遇到从某一操作记录表 中查出每一个用户的最新的一条记录。解决方法：1、首先按照时间降序 ，然后再按照ID分组select * from ( select * from A order by date desc)as agroup by a.id这样查询结果并不会得到想要的结果，因为mysql 5.7之后的子查询里就默认不会使用排序，如果想要子查询使用排序，则可以加上limit限制条件select * from ( select * from A order by date
SQL case when 遇到null值时的解决办法
Hi!Marvin!
10-27 3万+
(case when date is null then 'A' else 'B' end) '...' 最近刚刚入职，在熟悉项目的流程，熟悉数据库。在对数据库进行查询时，遇到了一个问题：查询结果中的某一列需要判断另一列是否为空的来确定值，自然就想到了case when，于是写出了下面的SQL：
mysql 查询上亿条数据库_Mysql查询最近一条记录的sql语句(优化篇)
weixin_34379989的博客
02-07 1136
Mysql查询最近一条记录的sql语句(优化篇)发布时间：2020-08-30 17:25:20来源：脚本之家阅读：77作者：一只阿木木下策——查询出结果后将时间排序后取第一条select * from awhere create_time<="2017-03-29 19:30:36"order by create_time desclimit 1这样做虽然可以取出当前时间最近的一条记录，但...
记录一次mysql 慢查询的处理
清风名
09-02 204
MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索引优化来实现MySQL的ORDER BY语句优化： 1、ORDER BY的索引优化。如果一个SQL语句形如： SELECT [column1],[column2],…. FROM [TAB...
mysql 取时间最近的一次记录吗_mysql如何获取距最近一次更新时间N个月(天)的记录？...
weixin_42503415的博客
01-18 1156
mysql> select * from mytable;iduseridpointlastUpdate11102018-01-02 00:00:0021102018-02-03 00:00:0031102018-02-04 00:00:0041102018-04-04 00:00:004 rows in set (0.00 sec)比如说距离最近一次更新时间3个月内的我尝试使用TIMEST...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值