我需要一个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
