Mysql查询 排序 分页

来到我身边 2014-12-24 05:29:52

SELECT c.id,c.credits,c.companyName,c.companyId
FROM((
SELECT cr.id,(CASE WHEN cr.credits IS NULL THEN 0 ELSE cr.credits END) AS credits ,c.name AS companyName,c.id AS companyId
FROM companies c
LEFT JOIN g_credit_company cr ON cr.companyId = c.id WHERE c.name IS NOT NULL ) AS c)
WHERE 1=1 AND c.companyName LIKE '%的公司%' ORDER BY c.credits DESC LIMIT 0,7 ;

上面SQL是按照 c.credits降序排序,如果 有多条记录的 c.credits的值相同,并且相同 c.credits的值被分在不同的页中,会出现怎么样的结果?
以下是执行结果:


SELECT c.id,c.credits,c.companyName,c.companyId
FROM((
SELECT cr.id,(CASE WHEN cr.credits IS NULL THEN 0 ELSE cr.credits END) AS credits ,c.name AS companyName,c.id AS companyId
FROM companies c
LEFT JOIN g_credit_company cr ON cr.companyId = c.id WHERE c.name IS NOT NULL ) AS c)
WHERE 1=1 AND c.companyName LIKE '%豆豆的公司%' ORDER BY c.credits DESC

执行结果


SELECT c.id,c.credits,c.companyName,c.companyId
FROM((
SELECT cr.id,(CASE WHEN cr.credits IS NULL THEN 0 ELSE cr.credits END) AS credits ,c.name AS companyName,c.id AS companyId
FROM companies c
LEFT JOIN g_credit_company cr ON cr.companyId = c.id WHERE c.name IS NOT NULL ) AS c)
WHERE 1=1 AND c.companyName LIKE '%豆豆的公司%' ORDER BY c.credits DESC LIMIT 0,7

执行结果:


SELECT c.id,c.credits,c.companyName,c.companyId
FROM((
SELECT cr.id,(CASE WHEN cr.credits IS NULL THEN 0 ELSE cr.credits END) AS credits ,c.name AS companyName,c.id AS companyId
FROM companies c
LEFT JOIN g_credit_company cr ON cr.companyId = c.id WHERE c.name IS NOT NULL ) AS c)
WHERE 1=1 AND c.companyName LIKE '%豆豆的公司%' ORDER BY c.credits DESC LIMIT 7,7

执行结果:
...全文
172 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
来到我身边 2014-12-26
  • 打赏
  • 举报
回复
我也是这样解决的,但是不知道为什么会出现上面的情况
引用 2 楼 wacld 的回复:
不过解决办法还是挺简单的,最后一句改下: ORDER BY c.credits,c.id DESC LIMIT 7,7
wacld 2014-12-25
  • 打赏
  • 举报
回复
不过解决办法还是挺简单的,最后一句改下: ORDER BY c.credits,c.id DESC LIMIT 7,7
wacld 2014-12-25
  • 打赏
  • 举报
回复
= =!~ 还有这样子的事情,表示真没有遇到过

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧