mysql - SQL联接4个表计数行

我有4张桌子，形式相似。这些表的结构如下：

id  team_id position_id  country_id
1       1     1              3
2       1     1              3
3       2     2              3
4       3     3              3

我可以用以下方法计算一张表的行数：

SELECT count(position_id) as count1, position_id
FROM players1
where country_id = 3
group by position_id;

得到的结果为：

position_id  count1
    1         54
    2         41
    3         39
    4         32

我想要加入4个表，并希望得到如下结果：

position_id  count1   count2   count3   count4
    1         54        42       51       61
    2         41        40       49       59
    3         39        29       44       50
    4         32        21       37       47

你能帮我写这个sql吗？

最佳答案

据我了解，您的问题。执行此Mysql查询。

SELECT
d1.position_id AS Positions_Id,
d1.count1 AS count1,
d2.count1 AS count2,
d3.count1 AS count3,
d4.count1 AS count4
FROM (
SELECT position_id, COUNT(position_id) AS count1
FROM players1
WHERE country_id=3
GROUP BY position_id) AS d1
LEFT JOIN (
SELECT position_id, COUNT(position_id) AS count1
FROM players2
WHERE country_id=3
GROUP BY position_id


) AS d2 ON d2.position_id = d1.position_id
LEFT JOIN (
SELECT position_id, COUNT(position_id) AS count1
FROM players3
WHERE country_id=3
GROUP BY position_id

) AS d3 ON d3.position_id = d1.position_id
LEFT JOIN (
SELECT position_id, COUNT(position_id) AS count1
FROM players4
WHERE country_id=3
GROUP BY position_id

) AS d4 ON d4.position_id = d1.position_id

关于mysql - SQL联接4个表计数行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59943981/