我有 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