问题描述
我有一个名为 sports 的表，其中包含运动列表、其他称为季节的列表，其中包含特定运动的季节和具有特定运动和季节比赛的比赛.
I've a table called sports that contains a list of list of sports, other called seasons that contains the seasons for a specific sport and competitions that have the competitions of a specific sport and season.
我需要一个 MySQL 查询来打印运动列表，其中包含每个赛季和比赛的数量.我的表结构:
I need one MySQL query to print the list of sports with how much seasons and competitions has each. My tables structure:
体育
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| slug | varchar(45) | NO | | NULL | |
| description | varchar(128) | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
季节
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id_sport | int(10) unsigned | NO | MUL | NULL | |
| name | varchar(32) | NO | | NULL | |
| slug | varchar(32) | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
比赛
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id_season | int(10) unsigned | NO | MUL | NULL | |
| name | varchar(32) | NO | | NULL | |
| slug | varchar(64) | NO | | NULL | |
| description | varchar(128) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
我的查询结果需要包含:sports.*, total_seasons (SUM ofseasons where seasons.id_sport=sports.id) 和 total_competitions (SUM of Competitions where Competitions.id_season=seasons.id ANDseasons.id_sport=sports.id).
The result of my query needs to contain: sports.*, total_seasons (SUM of seasons where seasons.id_sport=sports.id) and total_competitions (SUM of competitions where competitions.id_season=seasons.id AND seasons.id_sport=sports.id).
先谢谢你！
推荐答案
使用左连接代替内连接
select
sports.id,
sports.name,
sports.slug,
sports.description,
ifnull(count( distinct seasons.id ), 0) as DistinctSeasons,
ifnull(count( distinct competitions.id ), 0) as TotalCompetitions
from
sports
left join seasons on sports.id=seasons.id_sport
left join competitions on seasons.id = competitions.id_season
group by
sports.id;
这篇关于带有 2 个 COUNT() 其他表的 MySQL 查询，条件为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！