使用STRAIGHT_JOIN替换INNER JOIN,解决子查询排序失效问题

qq_40852612

于 2023-02-15 17:17:55 发布

阅读量232

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/qq_40852612/article/details/129047124

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

使用inner join 子查询时,排序字段失效(但是数据量大的时候有效)
原始sql:

SELECT
	`order_no`,
	`create_by`,
	`create_by_name`,
	`create_time`,
	`process_end_time`,
	`update_time`,
	`id`,
	`order_status`,
	`process_definition_id`,
	`process_node_id`
FROM
	incident AS return_data
INNER JOIN (
	SELECT
		id AS id_column
	FROM
		incident
	WHERE
		1 = 1
	AND `project_code` = "ITservice_P001"
	ORDER BY
		update_time DESC
	LIMIT 555,
	10
) AS ids ON return_data.id = ids.id_column

替换后的sql

SELECT
`order_no`,
 `create_by`,
 `create_by_name`,
 `create_time`,
 `process_end_time`,
 `update_time`,
 `id`,
 `order_status`,
 `process_definition_id`,
 `process_node_id`
FROM
	(
		SELECT
			id AS id_column
		FROM
			incident
		WHERE
			1 = 1
		
		AND `project_code` = "ITservice_P001"
		ORDER BY
			update_time DESC
		LIMIT 555,
		10
	) AS ids STRAIGHT_JOIN incident AS return_data ON return_data.id = ids.id_column