mysql中 group by 排序,left join 左连接排序
想让group by分组后在select中取到的数据是排序后的第一条数据
以前的代码:无法实现排序,
select
...
REC_a04.r0403
...
LEFT JOIN (
SELECT
a04.A00,
a04.r0403,
a04.r0404,
a04.r0401,
a04.r0402
FROM
rec_a04
GROUP BY a04.a00
order by a04.r0403
) REC_a04 ON REC_a04.A00 = ra.a00
这样看着像是根据 r0403排序的,其实并不然,子查询中是 先分组,再排序,分组时还没进行排序。
要的是想把 rec_a04 改成子查询,在子查询中先排序,然后再让group by来分组
LEFT JOIN (
SELECT
a04.A00 ,
a04.r0403,
a04.r0404,
a04.r0401,
a04.r0402
FROM(
select
rec_a04.A00,
rec_a04.r0403,
rec_a04.r0404,
rec_a04.r0401,
rec_a04.r0402
from
rec_a04
ORDER BY rec_a04.r0402 DESC
)a04
GROUP BY a04.a00
)REC_a04 on REC_a04.A00 = ra.a00
然而这样还是有点不行:查阅文档后发现需要加limit
– mysql5.7之后,子查询中的排序不生效,加上limit使排序生效
limit后的数字要大于查询出来的数据总数
LEFT JOIN (
SELECT
a04.A00 ,
a04.r0403,
a04.r0404,
a04.r0401,
a04.r0402
FROM(
select
rec_a04.A00,
rec_a04.r0403,
rec_a04.r0404,
rec_a04.r0401,
rec_a04.r0402
from
rec_a04
ORDER BY rec_a04.r0402 DESC
limit 100000
)a04
GROUP BY a04.a00
)REC_a04 on REC_a04.A00 = ra.a00