mysql - mysql中带有联接查询的两个表中每个单独的id的计数

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center。
                            
                        
                    
                
                                6年前关闭。
            
                    
我有两张桌子

表格1

id  team_id  fav_id
1    10        1
2    10        6
3    11        5
4    12        5
5    12        1
6    25        6

table_2

league_id   team_id   name
100          10        a
100          11        b
100          12        c
100          13        d
101          25        e

对于单个查询中的team_id中的每个league_id = 100，我需要一个table_2和join都来自count的所有fav_id的结果。

预期的结果，

league_id   team_id  name  count of(fav_id)
-------------------------------------------------
100          10       a          2
100          11       b          1
100          12       c          2
100          13       d          0

任何想法？

最佳答案

SELECT
    table_2.league_id,
    table_2.team_id,
    table_2.name,
    (SELECT COUNT(*) FROM table_1 WHERE table_1.team_id=table_2.team_id)
FROM
    table_2
WHERE
    table_2.league_id=100

关于mysql - mysql中带有联接查询的两个表中每个单独的id的计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15636685/