select * from
(
SELECT
cr.create_at_month as create_at,
cr.create_time as create_time,
c.id as client_id,
c.client_name,
g.id as gua_id,
g.name as gua_name,
cr.arrears as arrears
FROM client_arrears as cr
left join client c on cr.client_id = c.id
left join guarantee g on cr.gua_id = g.id
WHERE
cr.create_at_month in ('2019-07','2019-06') and
c.id in (56,57,58,59,60,63,64,65,66,67,89,91,92)
ORDER BY cr.id DESC
) AS cr2
可以得到排序后的结果:
但是在子查询之后,添加GROUP BY后,排序失效了
AS cr2 GROUP BY client_id
最后发现,子查询必须使用LIMIT进行约束才行
select * from
(
SELECT
cr.create_at_month as create_at,
cr.create_time as create_time,
c.id as client_id,
c.client_name,
g.id as gua_id,
g.name as gua_name,
cr.arrears as arrears
FROM client_arrears as cr
left join client c on cr.client_id = c.id
left join guarantee g on cr.gua_id = g.id
WHERE
cr.create_at_month in ('2019-07','2019-06') and
c.id in (56,57,58,59,60,63,64,65,66,67,89,91,92)
ORDER BY cr.id DESC LIMIT 10000
) AS cr2 GROUP BY client_id
