mysql - 如何统计不同时间的同一字段？

Question

我需要从我的数据库中提取特定球员的所有进球。我有这个表格设计:

匹配

id | home | away | result | round_id

玩家

id | first_name | last_name

目标

match_id | player_marker_id | type

GOAL_TYPE

id | description

本质上是表格 goal包含在 match 中得分的所有目标, 表绑定(bind)字段match_id作为FK的 match.id .领域player_marker_id是 FK的 player.id , 和 goal_type包含目标可以承担的所有类型，目标有 4 种类型:

1: first_goal
2: goal
3: pg
4: og

我需要做的是返回得分最高的球员，计算有多少first_goal, goal, pg .我不需要返回 og因为它对应于 auto goal.

我现在做了什么:

$sql = $this->db->prepare("SELECT
p.first_name AS player_first_name,
p.last_name AS player_last_name,
COUNT(*) AS goal_scored,
p.id AS player_id
FROM `match` m
INNER JOIN goal g ON g.match_id = m.id
INNER JOIN player p ON g.player_marker_id = p.id
WHERE m.round_id = :round_id AND g.type != 4
GROUP BY p.id
ORDER BY goal_scored DESC, player_last_name DESC");

此查询有效但未返回预期结果，事实上我得到:

{
    "player_first_name": "Ali",
    "player_last_name": "Sowe",
    "goal_scored": "21",
    "player_id": "246638"
},
{
    "player_first_name": "Sindrit",
    "player_last_name": "Guri",
    "goal_scored": "20",
    "player_id": "211786"
},

我会返回这个输出:

 {
    "player_first_name": "Ali",
    "player_last_name": "Sowe",
    "goal_scored": "21",
    "player_id": "246638", 
    "first_goal": "19",
    "goal": "1",
    "pg": "1"
}

我也试过这样做:

$sql = $this->db->prepare("SELECT
p.first_name AS player_first_name,
p.last_name AS player_last_name,
COUNT(*) AS goal_scored,
CASE g.type
  WHEN 1 THEN 'first_goal'
  WHEN 2 THEN 'goal'
  WHEN 3 THEN 'pg'
  WHEN 4 THEN 'og'
END AS goal_type,
p.id AS player_id
FROM `match` m
INNER JOIN goal g ON g.match_id = m.id
INNER JOIN player p ON g.player_marker_id = p.id
WHERE m.round_id = :round_id AND g.type != 4
GROUP BY p.id
ORDER BY goal_scored DESC, player_last_name DESC");

但这将返回:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'swp.g.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我想这不是正确的方法。

round_id是比赛所在的联赛。

Answer 1

您可以通过将 case 语句移动到计数聚合函数内部来仅计算给定目标类型的记录。试试这个:

SELECT
p.first_name AS player_first_name,
p.last_name AS player_last_name,
COUNT(*) AS goal_scored,
COUNT(case when g.type = 1 then 1 else null end) as first_goal,
COUNT(case when g.type = 2 then 1 else null end) as goal,
COUNT(case when g.type = 3 then 1 else null end) as pg,
p.id AS player_id
FROM `match` m
INNER JOIN goal g ON g.match_id = m.id
INNER JOIN player p ON g.player_marker_id = p.id
WHERE m.round_id = :round_id AND g.type != 4
GROUP BY p.id
ORDER BY goal_scored DESC, player_last_name DESC