mysql复杂的多对多内部连接

Question

我有 7 个表 db_category、db_city、db_locality、db_shop、db_shop_locality、db_shop_recommended、db_shop_views。

数据库类别

category_id    category_name    publish
    1             Apparel          1

数据库城市

city_id        city_name        publish
   1           bangalore           1

数据库位置

locality_id     locality_name     publish
    1            kalyan nagar       1
    2            Madiwala           1

数据库商店

shop_id   category_id   city_id   locality_id   shop_name   publish
   1          1            1          1           name         1
   2          1            1          2           name1        1
   3          1            1          1           name2        1
   4          1            1          1           name3        1

db_shop_recommended

recommended_id     category_id     shop_id    priority    publish
     1                 1             1           1           1
     2                 1             2           2           1

db_shop_views

views_id    shop_id    ip_addr    publish
    1          1      127.0.0.1      1
    2          2        ::1          1
    3          4      127.0.0.1      1
    4          4        ::1          1
    5          3        ::1          1

我想加入以上所有表格。加入的条件是，

1. 当publish=1 时显示db_shop 中的所有行并加入db_category、db_city 和db_locality 和 db_shop

2. 在 db_shop_recommended 中，priority=1 优先，依此类推。

3. 和shop_id不在db_shop_recommended中，从db_shop_views中统计shop_id不在db_shop_recommended中 谁的 shop_id 获得了更多浏览量。

我的结果应该如下所示，

shop_id   category_name   city_name   locality_name   shop_name
   1         Apparel      bangalore   kalyan nagar     name (result based on `db_shop_recommended` who's priority is more) 
   2         Apparel      bangalore     Madiwala       name1 (result based on `db_shop_recommended` who's priority is more) 
   4         Apparel      bangalore   kalyan nagar     name4 (result based on `db_shop_views` who's view count is more) 
   3         Apparel      bangalore   kalyan nagar     name3 (result based on `db_shop_views`)

我不知道如何加入和计算观看次数。以上方法是否可以加入。

Answer 1

经过大量搜索。我终于得到了答案。

SELECT DISTINCT s.shop_id, c.city_name, l.locality_name, ca.category_name, s.shop_name, s.shop_logo, s.cart_url, s.shop_about 来自 db_shop s INNER JOIN db_city c ON c.city_id = s.city_id INNER JOIN db_category ca ON ca.category_id = s.category_id INNER JOIN db_locality l ON l.locality_id = s.locality_id LEFT JOIN (SELECT * FROM db_shop_recommended r ORDER BY r.priority ASC) r1 ON s.shop_id = r1 .shop_id LEFT JOIN (select distinct g.shop_id, g.cnt from (select distinct shop_id, count(shop_id) cnt from db_shop_views group by shop_id) g 内连接(select max(s.cnt) max_cnt from(select distinct shop_id, count (shop_id) cnt 来自 db_shop_views group by shop_id) s) m on m.max_cnt = g.cnt) v ON s.shop_id = v.shop_id WHERE s.publish = 1