根据categoryId显示不同的计数最大数量 [英] Display distinct count max number of favourites based on categoryId
问题描述
我有5个表类别,商业,地铁,用户,收藏
类别
id | category_name
-------------------
1 |酒吧
2 | cafe
3 |午餐
4 |晚餐
商业
id | business_name | zip |地址| metro_id
------------------------------------------- -------------------
1 |桑德斯| 023232 | abc1 | 1
2 | ipc | 023232 | abc2 | 1
3 | mircleFoods | 023232 | abc3 | 1
4 |盐| 023232 | abc4 | 2
Metro
id | name
-------------------
1 |波士顿
2 | newYork
收藏
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
用户
id | firstName | LastName | userName |密码
--------------------------------------------- -------
1 |约翰|标记| jmark1 | 123
2 |约翰|标记| jmark2 | 123
3 |约翰|标记| jmark3 | 123
4 |约翰|标记| jmark4 | 123
i一直在尝试选择特定城市中收藏次数最多的类别,并显示其类别的商家名称favouried max。
i一直希望得到结果,例如
结果1
{
Metro-> boston
category-> bar
businessName-> sanders
favs-> 23}
结果2
{
Metro-> boston
category-> cafe
businessName-> sanders
favs-> 333}
i尝试了很多不同的查询,但现在已经开始尝试通过破解它来实现它这里有一些我试过的查询
1.
SELECT c.category_name,b.business_name,COUNT(f.user_id) FROM 业务b,类别c,收藏夹f
INNER JOIN business ON business.id = favorites。 business_id
INNER JOIN 类别 ON category.id = favourites.category_id
GROUP BY c.category_name,b.business_name
2.
SELECT c.category_name ,( SELECT COUNT(*) FROM 收藏夹) AS fs,b.business_name FROM (收藏夹f,商家b,类别c?) GROUP < span class =code-keyword> BY c.category_name LIMIT 0 , 1
3. <前la ng =SQL> SELECT f.category_id,COUNT(f.user_id) AS f FROM (收藏夹f,地铁m) GROUP BY category_id HAVING MAX(f.user_id)=( SELECT COUNT(user_id) FROM 收藏夹)
请指导我,我无法继续这个.Thankyou
如果你想为每个category_id
获得id
的MAX ,试试这个:
SELECT category_id,MAX(id)
FROM 收藏夹
GROUP BY category_id
如果你想为每个id
> category_id ,试试这个:
SELECT category_id,COUNT(id)
FROM 收藏夹
GROUP BY category_id
或使用
SELECT category_id,COUNT( DISTINCT id)
FROM 收藏夹
GROUP BY category_id
获取不同的ID'' s。
更多: MySQL聚合函数 [ ^ ]
SELECT c.category_name,b.business_ name,COUNT(f.user_id)
FROM 收藏夹f LEFT JOIN business b ON f.business_id = b.id LEFT JOIN 类别c ON c.id = f.category_id
GROUP BY c.category_name,b.business_name
有关加入的更多信息[ ^ ], SQL连接的可视化表示 [ ^ ]。
[/编辑]
i have 5 tables category ,business ,metro ,user,favourites
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
i have been trying to select a category with the highest count of favs in a particular city and display the business name which has its category favouried max.
i have been hoping to get result e.g
RESULT 1
{
Metro->boston
category->bar
businessName->sanders
favs->23}
RESULT 2
{
Metro->boston
category->cafe
businessName->sanders
favs->333}
i have tried many different queries but now have started trying to implement it by breaking it up here is some of the queries i have tried
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)
Please kindly guide me , i am having trouble proceeding with this .Thankyou
If you want to get MAX ofid
for eachcategory_id
, try this:
SELECT category_id, MAX(id) FROM Favourites GROUP BY category_id
If you want to countid
for eachcategory_id
, try this:
SELECT category_id, COUNT(id) FROM Favourites GROUP BY category_id
or use
SELECT category_id, COUNT(DISTINCT id) FROM Favourites GROUP BY category_id
to get distinct id''s.
More: MySQL aggregate functions[^]
[EDIT #2]
SELECT c.category_name, b.business_name, COUNT(f.user_id) FROM favourites f LEFT JOIN business b ON f.business_id=b.id LEFT JOIN category c ON c.id=f.category_id GROUP BY c.category_name, b.business_name
More about JOIN''s[^], Visual Representation of SQL Joins[^].
[/EDIT]
这篇关于根据categoryId显示不同的计数最大数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!