mysql order by 失效_sql子查询 order by失效问题

一、问题描述  三表联合查询  按主表的时间倒叙并分页

下面sql语句 ORDER BY失效 ，如果把 ORDER BY  放在最后 比如有20条数据 第一页会是10 -1 第二页是20-11  分页不正确

查询资料发现：在mysql5.7中，如果不加limit，系统会把order by优化掉。

在mysql5.7手册的9.2.2.1中有解释：

子查询的优化是使用半连接的策略完成的(The optimizer uses semi-join strategies to improve subquery execution)

使用半连接进行优化，子查询语句必须满足一些标准(In MySQL, a subquery must satisfy these criteria to be handledas a semi-join)。

其中一个标准是:必须不是一个包含了limit和order by的语句(It must not have ORDER BY with LIMIT.)

解决方法一：子查询里外都排序

解决方法二：在嵌入一个子查询(好像也是里外都排序)

SELECT * FROM (

SELECT * FROM (

SELECT * FROM zs_safe_confess B WHERE B.ENABLE = 1 ORDER BY B.CREATE_TIME DESC, B.UPDATE_TIME DESC LIMIT 0,10) A

LEFT JOIN zs_safe_confess_content C ON A.ID = C.CSAFE_ID)D

LEFT JOIN zs_resources ON D.ID = zs_resources.ID AND zs_resources.TYPE = 10 AND zs_resources.ENABLE = 1

ORDER BY D.CREATE_TIME DESC, D.UPDATE_TIME DESC

SELECT A.*,

zs_safe_confess_content.CID,

zs_safe_confess_content.CSAFE_ID,

zs_safe_confess_content.CSTATUS,

zs_safe_confess_content.CCONTENT,

zs_safe_confess_content.CCHECK_RESULT,

zs_safe_confess_content.CCREATE_TIME,

zs_safe_confess_content.CUPDATE_TIME,

zs_safe_confess_content.CUSER_ID,

zs_safe_confess_content.CUSER_NAME,

zs_safe_confess_content.CENABLE,

zs_safe_confess_content.CBACKUP_01,

zs_safe_confess_content.CBACKUP_02,

zs_safe_confess_content.CBACKUP_03,

zs_resources.RID AS RID,

zs_resources.ID AS ID,

zs_resources.TYPE AS RTYPE,

zs_resources.URL AS RURL,

zs_resources.RESOURCE_CODE AS RRESOURCE_CODE,

zs_resources.FILE_NAME AS RFILE_NAME,

zs_resources.FILE_SIZE AS RFILE_SIZE,

zs_resources.CREATOR AS RCREATOR,

zs_resources.CREATETIME AS RCREATETIME,

zs_resources.MODIFIER AS RMODIFIER,

zs_resources.UPDATETIME AS RUPDATETIME,

zs_resources.ENABLE AS RENABLE FROM (

SELECT

zs_safe_confess.ID,

zs_safe_confess.SAFE_NAME, zs_safe_confess.DEPT_NAME,

zs_safe_confess.DEPT_ID, zs_safe_confess.PROJECT_NAME,

zs_safe_confess.PROJECT_NUMBER,

zs_safe_confess.PROJECT_PERSON,

zs_safe_confess.WORK_ADDRESS, zs_safe_confess.WORK_NUMBER,

zs_safe_confess.COMPANY_PERSON, zs_safe_confess.COMPANY_PHONE,

zs_safe_confess.FACTORY_PERSON,

zs_safe_confess.FACTORY_PHONE,

zs_safe_confess.DANGER_DISCERN, zs_safe_confess.ENVIRONMENT_DISCREN,

zs_safe_confess.DANGER_MEASURES,zs_safe_confess.ENVIRONMENT_MEASURES,

zs_safe_confess.DANGER_MANAGER, zs_safe_confess.MANAGER_TIME,

zs_safe_confess.SCENE_COMFIRM, zs_safe_confess.SCENE_COMFIRM_TIME,

zs_safe_confess.COMFIRM_CHECK_PERSON,

zs_safe_confess.COMFIRM_CHECK_TIME,

zs_safe_confess.TEST_BEFORE_PERSON, zs_safe_confess.TEST_BEFORE_TIME,

zs_safe_confess.TEST_PERSON,

zs_safe_confess.TEST_TIME,

zs_safe_confess.TEST_AFTER_PERSON,zs_safe_confess.TEST_AFTER_TIME,zs_safe_confess.STATUS,zs_safe_confess.TYPE

,zs_safe_confess.CREATE_TIME,zs_safe_confess.UPDATE_TIME,zs_safe_confess.USER_ID,zs_safe_confess.USER_NAME,zs_safe_confess.ENABLE

,zs_safe_confess.BACKUP_01,zs_safe_confess.BACKUP_02,zs_safe_confess.BACKUP_03

FROM zs_safe_confess WHERE zs_safe_confess.ENABLE = 1 ORDER BY CREATE_TIME DESC, UPDATE_TIME DESC LIMIT 0,10) A

LEFT JOIN zs_safe_confess_content ON A.ID = zs_safe_confess_content.CSAFE_ID

LEFT JOIN zs_resources ON A.ID = zs_resources.ID AND zs_resources.TYPE = 10 AND zs_resources.ENABLE = 1 (ORDER BY A.CREATE_TIME DESC, A.UPDATE_TIME DESC)(这是解决方法一)