时间戳

首页 > 解决方案 > SQL Query问题——六表难查询

mysql - SQL Query问题——六表难查询

问题描述

我需要 sql 专家的帮助。我的 sql 查询有问题。我有六个 MySQL 表：

pcs_persons (table of players)

+----+------------+------------+
| id |  firstname |  lastname  |
+----+------------+------------+
|  1 |  John      |  McClane   |
|  2 |  Jack      |  Marriott  |
|  3 |  Billy     |  Bravo     |
|  4 |  Archie    |  MacDonald |
+----+------------+------------+

pcs_matchs (table of match results)

+----+-------------------+-------------------+---------+------------+------------+
| id |  id_candidate_dom |  id_candidate_ext |  id_day |  id_season |  id_compet |
+----+-------------------+-------------------+---------+------------+------------+
|  1 |                 1 |                 2 |       1 |          1 |          1 |
|  2 |                 3 |                 4 |       1 |          1 |          1 |
|  3 |                 2 |                 3 |       2 |          1 |          1 |
|  4 |                 4 |                 1 |       2 |          1 |          1 |
|  5 |                 1 |                 7 |       1 |          2 |          3 |
|  6 |                 6 |                 3 |       2 |          2 |          5 |
+----+-------------------+-------------------+---------+------------+------------+


pcs_lineup (table of those players who were selected to the match squad as starter - type 2, or as substitute - type 3)

+----+-----------+----------+------------+-------+
| id |  id_match |  id_club |  id_person |  type |
+----+-----------+----------+------------+-------+
|  1 |         1 |        1 |          1 |     2 |
|  2 |         1 |        1 |          2 |     3 |
|  3 |         1 |        2 |          3 |     2 |
|  4 |         1 |        2 |          4 |     3 |
+----+-----------+----------+------------+-------+

pcs_goals (table of scored goals by players)

| id |  id_match |  id_person |  id_club |  goal_min |
+----+-----------+------------+----------+-----------+
|  1 |         1 |          1 |        1 |        23 |
|  2 |         1 |          1 |        1 |        48 |
|  3 |         1 |          3 |        2 |        56 |
|  4 |         1 |          4 |        2 |        89 |
+----+-----------+------------+----------+-----------+


pcs_cards (table of received cards by players)

| id |  id_match |  id_person |  id_club |  card_min |  card_yellow |  card_red |
+----+-----------+------------+----------+-----------+--------------+-----------+
|  1 |         1 |          1 |        1 |        12 |            1 |           |
|  2 |         1 |          1 |        1 |        43 |            1 |           |
|  3 |         1 |          3 |        2 |        78 |               |         1 |
|  4 |         1 |          4 |        2 |        91 |            1 |           |
+----+-----------+------------+----------+-----------+--------------+-----------+

pcs_subs (table of substitutions)

| id |  id_match |  id_club |  id_person_in |  id_person_out |  subs_min |
+----+-----------+----------+---------------+----------------+-----------+
|  1 |         1 |        1 |             7 |              1 |        82 |
|  2 |         1 |        1 |             8 |              2 |        85 |
|  3 |         1 |        2 |             5 |              3 |        89 |
|  4 |         1 |        2 |             6 |              4 |        91 |
+----+-----------+----------+---------------+----------------+-----------+

我当前的查询在这里：

SELECT pcs_lineup.id_person, pcs_lineup.id_club, pcs_lineup.type,
pcs_persons.lastname, pcs_persons.firstname, count( pcs_lineup.id_person) AS apps, count(pcs_subs.id_person_in) AS subs
FROM pcs_lineup
JOIN pcs_matchs ON pcs_matchs.id = pcs_lineup.id_match
JOIN pcs_persons ON pcs_persons.id = pcs_lineup.id_person
LEFT JOIN pcs_subs ON pcs_subs.id_person_in = pcs_lineup.id_person
WHERE pcs_lineup.id_club =2
AND pcs_matchs.id_compet =1
AND pcs_matchs.id_season =1
AND pcs_lineup.type = 2 OR pcs_subs.id_person_in IS NOT NULL AND pcs_subs.id_club =2
GROUP BY id_person

我目前的结果结构（作为首发或替补出场的球员名单，刚坐在替补席上的球员不计算在内）

+-----------+----------+-------+-----------+------------+-------+-------+
| id_person |  id_club |  type |  lastname |  firstname |  apps |  subs |
+-----------+----------+-------+-----------+------------+-------+-------+

我想在结果中添加额外的列（进球、黄牌、红牌），但我不知道怎么做。

期望结果的结构：

+-----------+----------+-------+-----------+------------+-------+----------+-------+--------+---------------+------------+
| id_person |  id_club |  type |  lastname |  firstname |  apps |  starter |  subs |  goals |  yellow cards |  red_cards |
+-----------+----------+-------+-----------+------------+-------+----------+-------+--------+---------------+------------+

我希望一些专家可以帮助我，因为我不知道如何加入这些表格以获得所需的结果。非常感谢！

修改代码（效果不好）

SELECT pcs_lineup.id_person
      ,pcs_lineup.id_club
      ,pcs_lineup.type
      ,pcs_persons.lastname
      ,pcs_persons.firstname
      ,count( pcs_lineup.id_person) AS apps
      ,CASE WHEN pcs_lineup.type = 2 THEN 'YES' END starter
      ,count(pcs_subs.id_person_in) AS subs
      ,count(pcs_goals.goal_min) AS goals
      ,count(card_yellow) as "Yellow Cards"
      ,count(card_red) as "Red Card"
FROM pcs_lineup
JOIN pcs_matchs ON pcs_matchs.id = pcs_lineup.id_match
JOIN pcs_persons ON pcs_persons.id = pcs_lineup.id_person
LEFT JOIN pcs_subs ON pcs_subs.id_person_in = pcs_lineup.id_person
LEFT JOIN pcs_goals ON pcs_goals.id_match = pcs_matchs.id
                    AND pcs_persons.id = pcs_goals.id_person
LEFT JOIN pcs_cards ON pcs_cards.id_match = pcs_matchs.id
                    AND pcs_cards.id_person = pcs_persons.id
                    AND pcs_goals.id_club = pcs_cards.id_club
WHERE pcs_lineup.id_club =2
AND pcs_matchs.id_compet =1
AND pcs_matchs.id_season =1
AND pcs_subs.id_person_in IS NOT NULL AND pcs_subs.id_club =2
GROUP BY id_person

标签： mysqlsqldatabasejoin

解决方案

您需要再加入 2 个表，即 pcs_goals 和 pcs_cards -

SELECT pcs_lineup.id_person
      ,pcs_lineup.id_club
      ,pcs_lineup.type
      ,pcs_persons.lastname
      ,pcs_persons.firstname
      ,count( pcs_lineup.id_person) AS apps
      ,CASE WHEN pcs_lineup.type = 2 THEN 'YES' END starter
      ,count(pcs_subs.id_person_in) AS subs
      ,count(pcs_goals.goals_min) AS goals
      ,count(card_yellow) as "Yellow Cards"
      ,count(card_red) as "Red Card"
FROM pcs_lineup
JOIN pcs_matchs ON pcs_matchs.id = pcs_lineup.id_match
JOIN pcs_persons ON pcs_persons.id = pcs_lineup.id_person
LEFT JOIN pcs_subs ON pcs_subs.id_person_in = pcs_lineup.id_person
LEFT JOIN pcs_goals ON pcs_goals.id_match = pcs_matchs.id
                    AND pcs_persons.id = pcs_matchs.id_person
LEFT JOIN pcs_cards ON pcs_cards.id_match = pcs_matchs.id
                    AND pcs_cards.id_person = pcs_persons.id
                    AND pcs_goals.id_club = pcs_cards.id_club
WHERE pcs_lineup.id_club =2
AND pcs_matchs.id_compet =1
AND pcs_matchs.id_season =1
AND pcs_subs.id_person_in IS NOT NULL AND pcs_subs.id_club =2
GROUP BY id_person

我不确定您所说的入门专栏是什么意思。

推荐阅读