带有 2 个 COUNT() 其他表的 MySQL 查询，条件为 [英] MySQL query with 2 COUNT() of other tables with where conditions

问题描述

我有一个名为 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 查询，条件为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！