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

14 篇文章 0 订阅
14 篇文章 0 订阅
SELECTa.id AS"id", a.code AS"code", a.nameAS "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.nameAS "department.name", projectManager.id AS"projectManager.id", projectManager.nameAS "projectManager.name", projectManager.mobile AS"projectManager.mobile", marketer.id AS"marketer.id", marketer.nameAS "marketer.name", marketer.mobile AS"marketer.mobile", customer.id AS"customer.id", customer.code AS"customer.code", customer.nameAS "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* FROMps_project WHEREdel_flag = 0 ORDERBY code DESClimit 2,2) a
LEFTJOIN ps_customer customer ONa.customerid = customer.id
LEFTJOIN sys_office department ONa.departmentid = department.id
LEFTJOIN sys_user projectManager ONa.project_managerid = projectManager.id
LEFTJOIN sys_user marketer ONa.marketerid = marketer.id
LEFTJOIN ps_project_push push ONa.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打乱的结果顺序再做一次排序,这样就能得到我们想要的结果了。

SELECTa.id AS"id", a.code AS"code", a.nameAS "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.nameAS "department.name", projectManager.id AS"projectManager.id", projectManager.nameAS "projectManager.name", projectManager.mobile AS"projectManager.mobile", marketer.id AS"marketer.id", marketer.nameAS "marketer.name", marketer.mobile AS"marketer.mobile", customer.id AS"customer.id", customer.code AS"customer.code", customer.nameAS "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 WHEREdel_flag = 0 ORDERBY code DESC limit 0,2 ) a
LEFT JOIN ps_customer customer ONa.customerid = customer.id
LEFT JOIN sys_office department ONa.departmentid = department.id
LEFT JOIN sys_user projectManager ONa.project_managerid = projectManager.id
LEFT JOIN sys_user marketer ONa.marketerid = marketer.id
LEFT JOIN ps_project_push push ONa.id = push.projectid 
ANDpush.del_flag = 0 ANDcustomer.del_flag= 0 ANDdepartment.del_flag= 0 ANDprojectManager.del_flag= 0 ANDmarketer.del_flag= 0
ORDERBY code DESC


  • 2
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值