mysql连表排序

Question

表A
id      info   
1        message1
2        message2
3        message3


表B
id         goods_id
1            1
1            2
2            3
3            4

AB表id连表，查询结果根据B表的相同id个数排序，例如id=1的在B表有两个，排在前面，id=2和id=3的只有一个，排在后面，请问mysql排序语句order by该怎么写？

Answer 1

select A.id, A.info, count(B.goods_id) from A inner join B on A.id = B.id group by A.id order by count(B.goods_id) desc

Answer 2

Let’s talk about the available sql statements. The performance is not very good. There is one more query for table b.

select a.*, b.*
from a
  inner join b on a.id = b.id
  inner join (
    select id, count(*) as cnt
    from b
    group by id
  ) c on a.id = c.id
order by c.cnt, a.id

Answer 3

If the amount of data is large.
If it were me, I would reconsider the rationality of the demand. Push it away if you can.
If you can’t push it away, add a redundant field goods_count to table a. Then create a joint index.