SQL查询问题-具有六个表的困难查询 [英] SQL Query problem - difficult query with six tables

问题描述

我需要SQL专家的帮助.我的SQL查询有问题.我有六个MySQL表:

I need a help of a sql expert. I have a problem with my sql query. I have six MySQL tables:

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

我当前的成绩结构(作为首发阵容成员或替补球员的球员列表，坐在板凳上的球员不计算在内)

My current result structure (list of players who played as member of starting lineup or played as substitutes, players who just sat on the bench not counted)

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

我想在结果中添加额外的列(目标，黄牌，红牌)，但我不知道怎么做.

I would like to add extra columns (goals, yellow cards, red cards) to the result, but I don't know how.

所需结果的结构:

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

我希望一些专家可以为我提供帮助，因为我不知道如何才能将这些表加入以获得期望的结果.非常感谢！

I hope that some expert could help for me, because I have no idea how could I join these tables for the desired result. Many thanks!

修改后的代码(结果不好)

Modified code (results are not good)

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

推荐答案

您需要再连接2个表，即pcs_goals和pcs_cards-

You need to join 2 more tables i.e. pcs_goals and 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

我不确定入门专栏"是什么意思.

I am not sure what do you mean by starter column.

这篇关于SQL查询问题-具有六个表的困难查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！