mysql - 根据 categoryId 显示不同的计数最大收藏夹数量

Question

我有 5 个表类别，业务，地铁，用户，收藏夹

Category
id | category_name
-------------------
 1  | bar
 2  | cafe
 3  | lunch
 4  | dinner

Business
id | business_name | zip        | address            |metro_id
--------------------------------------------------------------
1  | sanders      | 023232      | abc1               | 1
2  | ipc          | 023232      | abc2               | 1
3  | mircleFoods  | 023232      | abc3               | 1
4  | salt         | 023232      | abc4               | 2

Metro
id | name
-------------------
1  | boston
2  | newYork


Favourites
id | user_id | business_id | metro_id | category_id
----------------------------------------------------
1  | 1       | 1           | 3         | 1
2  | 1       | 1           | 4         | 2
3  | 1       | 1           | 3         | 1
4  | 1       | 1           | 5         | 2

User
id | firstName | LastName |userName   | Password
----------------------------------------------------
1  | john   | mark   | jmark1       |123
2  | john  | mark   | jmark2        | 123
3  | john   | mark  | jmark3        | 123
4  | john   | mark  | jmark4        | 123

我一直在尝试选择一个特定城市中收藏次数最多的类别，并显示其类别最受喜爱的公司名称。

我一直希望得到结果例如

RESULT 1{
Metro->boston
category->bar
businessName->sanders
favs->23}


RESULT 2{
Metro->boston
category->cafe
businessName->sanders
favs->333}

我已经尝试过很多不同的查询，但现在已经开始尝试通过分解来实现它，这里是我尝试过的一些查询

1.    SELECT c.category_name,b.business_name,COUNT(f.user_id) FROM business b, category c, favourites f
INNER JOIN business ON business.id=favourites.business_id
INNER JOIN category ON category.id=favourites.category_id
GROUP BY c.category_name,b.business_name

2.    SELECT c.category_name,(SELECT COUNT(*) FROM favourites) AS fs,b.business_name FROM(favourites f,business b,category c) GROUP BY c.category_name LIMIT 0,1

3.    SELECT f.category_id, COUNT(f.user_id) AS f FROM (favourites f,metro m)  GROUP BY category_id HAVING MAX(f.user_id)=(SELECT COUNT(user_id) FROM favourites)

Answer 1

试试这个:

SELECT
    b.business_name,
    m.name,
    c.category_name,
    favs
FROM (
    SELECT
        business_id,
        metro_id,
        category_id,
        COUNT(*) as favs
    FROM
        Favourites 
    WHERE
        metro_id = 1
    GROUP BY
        business_id,
        category_id
) as f
INNER JOIN Metro as m
    ON m.id = f.metro_id
INNER JOIN Business as b
    ON b.id = f.business_id
INNER JOIN Category as c
    ON c.id = f.category_id
ORDER BY
    favs
LIMIT 1

它应该显示“在特定城市中收藏次数最多的类别，并显示其类别收藏次数最多的公司名称。”

更新:新要求“没有属于一个企业的每个类别的最大收藏。”

SELECT
    b.business_name,
    m.name,
    c.category_name,
    favs
FROM (
    SELECT
        category_id,
        business_id,
        metro_id,
        COUNT(*) as favs
    FROM
        Favourites 
    WHERE
        business_id = 1
    GROUP BY
        category_id,
        metro_id
    ORDER BY
        favs DESC
    LIMIT 1
) as f
INNER JOIN Metro as m
    ON m.id = f.metro_id
INNER JOIN Business as b
    ON b.id = f.business_id
INNER JOIN Category as c
    ON c.id = f.category_id