mysql - SQL 表 - 根据 table1 值计算 table2 中的行

Question

---

我有两个具有相同列名的 MySQL 表:

table1 = id, company

table2 = id, 公司

我想在表 1 中添加一列，其中包含表 2 中包含相同 ID 和公司的行数。

例如:

table1
id, company
1, 12
2, 12
3, 14
4, 14

table2
id, company
1, 4
1, 5
1, 12
1, 12
3, 14

我希望获得:

table1
id, company, table2count
1, 12, 2
2, 12, 0
3, 14, 1
4, 14, 0

---

由于某些原因，即使未找到匹配项，下面提出的查询也会返回计数 = 1。我一直在使用 sqldf 库在 R 中进行测试:

> table1
           id company
1  2187163455    3509
2  1762086305    5824
3  1762086305    9909
4   457591705    9909
5   456203419    9909
6  1877752457    9909
7   442780095    9909
8   471442042    9909
9   457590444    9909
10 3879669310    9909
> table2
            id company
 1:      86246    9909
 2:      86246    5824
 3:  442780095    7205
 4:      86246    5558
 5: 1762086305    5824
 6:      86246    9909
 7: 1762086305    9909
 8:      86246    3509
 9:      86246    3509
10:      86246    3504
> sql_query = " SELECT table1.id, table1.company, COUNT(*) AS table2count
+ FROM table1
+ LEFT JOIN table2 ON table1.id=table2.id AND table1.company = table2.company
+ GROUP BY table1.id"
> sqldf(sql_query)
          id company table2count
1  442780095    9909           1
2  456203419    9909           1
3  457590444    9909           1
4  457591705    9909           1
5  471442042    9909           1
6 1762086305    9909           2
7 1877752457    9909           1
8 2187163455    3509           1
9 3879669310    9909           1

Answer 1

SELECT table1.id, table1.company, COUNT(  table2.id  ) AS table2count
FROM table1
LEFT JOIN table2 ON table1.id=table2.id and table1.company = table2.company
GROUP BY table1.id