SQL 左连接(left join) 排序 分页 中遇到的未按理想状态排序分页的解决方案

原创
2016/01/20 14:18
阅读数 4.8K
SELECT a.id AS "id", a.code AS "code", a.name AS "name", a.type AS "type", a.importance_degree AS "importanceDegree", a.tech_state AS "techState", a.customerid AS "customerid", a.departmentid AS "departmentid", a.project_managerid AS "projectManagerid", a.plan_starttime AS "planStarttime", a.plan_endtime AS "planEndtime", a.state AS "state", a.act_starttime AS "actStarttime", a.act_endtime AS "actEndtime", a.sending_time AS "sendingTime", a.old_plan_starttime AS "oldPlanStarttime", a.old_plan_endtime AS "oldPlanEndtime", a.delivery_time AS "deliveryTime", a.complete_status AS "completeStatus", a.marketerid AS "marketerid", a.create_by AS "createBy.id", a.create_date AS "createDate.id", a.update_by AS "updateBy.id", a.update_date AS "updateDate.id", a.remarks AS "remarks", a.del_flag AS "delFlag", department.id AS "department.id", department.name AS "department.name", projectManager.id AS "projectManager.id", projectManager.name AS "projectManager.name", projectManager.mobile AS "projectManager.mobile", marketer.id AS "marketer.id", marketer.name AS "marketer.name", marketer.mobile AS "marketer.mobile", customer.id AS "customer.id", customer.code AS "customer.code", customer.name AS "customer.name", push.id AS "push.id", push.projectid AS "push.project.id", push.userid AS "push.user.id", push.is_readed AS "push.isReaded"
FROM (SELECT * FROM ps_project WHERE del_flag = 0 ORDER BY code DESC limit 2,2) a
LEFT JOIN ps_customer customer ON a.customerid = customer.id
LEFT JOIN sys_office department ON a.departmentid = department.id
LEFT JOIN sys_user projectManager ON a.project_managerid = projectManager.id
LEFT JOIN sys_user marketer ON a.marketerid = marketer.id
LEFT JOIN ps_project_push push ON a.id = push.projectid 
AND push.del_flag = 0 
AND customer.del_flag= 0 
AND department.del_flag= 0 
AND projectManager.del_flag= 0 
AND marketer.del_flag= 0

语句目标:
    以主表排序后并进行分页,而后再去连接其它表
出现问题:
    最终主表并没有按照预想进行顺序输出,但是分页的数据是正确的。
来自  stackflow 解答:

No, the JOIN by order is changed during optimization.

我的解决办法(经测试有效)

分页排序在主表中进行,这样就mysql在执行的过程中分根据我们的理想按字段排序且选出指定分页。

但是在Join时,mysql系统做了优化,所以最终出来的结果又是乱序,此时,对最终被mysql Join打乱的结果顺序再做一次排序,这样就能得到我们想要的结果了。

SELECT a.id AS "id", a.code AS "code", a.name AS "name", a.type AS "type", a.importance_degree AS "importanceDegree", a.tech_state AS "techState", a.customerid AS "customerid", a.departmentid AS "departmentid", a.project_managerid AS "projectManagerid", a.plan_starttime AS "planStarttime", a.plan_endtime AS "planEndtime", a.state AS "state", a.act_starttime AS "actStarttime", a.act_endtime AS "actEndtime", a.sending_time AS "sendingTime", a.old_plan_starttime AS "oldPlanStarttime", a.old_plan_endtime AS "oldPlanEndtime", a.delivery_time AS "deliveryTime", a.complete_status AS "completeStatus", a.marketerid AS "marketerid", a.create_by AS "createBy.id", a.create_date AS "createDate.id", a.update_by AS "updateBy.id", a.update_date AS "updateDate.id", a.remarks AS "remarks", a.del_flag AS "delFlag", department.id AS "department.id", department.name AS "department.name", projectManager.id AS "projectManager.id", projectManager.name AS "projectManager.name", projectManager.mobile AS "projectManager.mobile", marketer.id AS "marketer.id", marketer.name AS "marketer.name", marketer.mobile AS "marketer.mobile", customer.id AS "customer.id", customer.code AS "customer.code", customer.name AS "customer.name", push.id AS "push.id", push.projectid AS "push.project.id", push.userid AS "push.user.id", push.is_readed AS "push.isReaded"
FROM (SELECT * FROM ps_project WHERE del_flag = 0 ORDER BY code DESC  limit 0,2 ) a
LEFT  JOIN ps_customer customer ON a.customerid = customer.id
LEFT  JOIN sys_office department ON a.departmentid = department.id
LEFT  JOIN sys_user projectManager ON a.project_managerid = projectManager.id
LEFT  JOIN sys_user marketer ON a.marketerid = marketer.id
LEFT  JOIN ps_project_push push ON a.id = push.projectid 
AND push.del_flag = 0 AND customer.del_flag= 0 AND department.del_flag= 0 AND projectManager.del_flag= 0 AND marketer.del_flag= 0
ORDER BY code DESC




展开阅读全文
打赏
1
2 收藏
分享
加载中
确实加了别的筛选条件好像就不行了,比如现在需要让副表满足某些条件,此时主表的数据已经被分页了,副表加了条件只是在主表分页后的结果上做过滤。。不知道我有没有说清楚
2019/07/22 16:23
回复
举报
大东家博主
那就要进行调试了,一般这种情况where在前的吧,不行就只能再嵌套
2019/07/23 10:28
回复
举报
大东家博主

引用来自“萨达姆1”的评论

加上其他的筛选条件就满足不了条件了
可以的
2018/12/20 21:38
回复
举报
加上其他的筛选条件就满足不了条件了
2018/12/20 15:34
回复
举报
更多评论
打赏
4 评论
2 收藏
1
分享
返回顶部
顶部