根据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 of id for each category_id, try this:

SELECT category_id, MAX(id)
FROM Favourites
GROUP BY category_id

If you want to count id for each category_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屋！