mysql 查询连接两个表

Question

表1

person_id| location_id | field a | field b |friend_id

表2

location_id | location_name

friend_id 是给定人喜欢的人的 ID(它是可选字段，默认值为零)

如何查询两个表以获得以下内容:

点赞人数最多的人的location_name，以此类推，按降序排列。

Answer 1

首先，确保您在 Friend_ID 列上有索引，以便可以在查询中对其进行优化

select
      P.Person_ID,
      P.FieldA,
      P.FieldB,
      L.Location_Name,
      ByPopularity.Popular
   from
      ( select T1.friend_id, count(*) as Popular
           from Table1 T1
           group by T1.friend_id
           order by Popular DESC ) ByPopularity

         JOIN Table1 P
            on ByPopularity.Friend_ID = P.person_ID

            Join Table2 L
               on P.Location_ID = L.Location_ID

编辑 - 关于如何根据人员来源获取最受欢迎的位置的评论

select
      L.Location_Name,
      ByPopularity.PopularLocation
   from
      ( select T1.Location_ID, count(*) as PopularLocation
           from Table1 T1
           group by T1.Location_ID
           order by PopularLocation DESC ) ByPopularity

           Join Table2 L
               on ByPopularity.Location_ID = L.Location_ID