mysql - 无法执行 GROUP_CONCAT
问题描述
我试图返回GROUP_CONCAT
与连接记录关联的 5 个匹配项,我尝试按以下方式设置查询:
$sql = $this->db->prepare("SELECT l.*,
t.name as team_name,
r.name AS rank_name,
r.color AS rank_color,
GROUP_CONCAT(DISTINCT m.* LIMIT 5, ORDER BY m.id) AS last_five_matches,
FROM league_ranking l
LEFT JOIN team t ON l.team_id = t.id
LEFT JOIN competition_ranks r ON l.rank = r.id
LEFT JOIN `match` m ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5");
如您所见,我想返回 5 个状态 = 5 的匹配项。
问题是我得到:
SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以获取在 '* LIMIT 5, ORDER BY m.id) AS last_five_matches, FROM League_ranking l 附近使用的正确语法
我不明白问题出在哪里GROUP_CONCAT
,请注意,如果我删除GROUP_CONCAT
查询工作,有什么帮助吗?
更新
这是已编辑的查询:
"SELECT l.*,
t.name as team_name,
r.name AS rank_name,
r.color AS rank_color,
GROUP_CONCAT(DISTINCT m.id ORDER BY m.id) AS last_five_matches
FROM league_ranking l
LEFT JOIN team t ON l.team_id = t.id
LEFT JOIN competition_ranks r ON l.rank = r.id
LEFT JOIN `match` m ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5
GROUP BY team_name"
我得到:
语法错误或访问冲突:1055 SELECT 列表的表达式 #1 不在 GROUP BY 子句中,并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列“swp.l.position”;这与 sql_mode=only_full_group_by 不兼容
解决方案
我相信你想要:
SELECT t.name as team_name, r.name AS rank_name, r.color AS rank_color,
SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT m.id ORDER BY m.id), ',', 5) AS last_five_matches
FROM team t LEFT JOIN
league_ranking l
ON l.team_id = t.id LEFT JOIN
competition_ranks r
ON l.rank = r.id LEFT JOIN
`match` m
ON m.home_team_id = l.team_id OR m.away_team_id = l.team_id
WHERE l.round_id = :round_id AND m.status = 5
GROUP BY t.name, r.name, r.color;
笔记:
GROUP BY
键与SELECT
.- 我删除了
l.*
.GROUP BY
在查询中没有意义。 GROUP_CONCAT()
不支持LIMIT
。听起来是个好主意,但它不存在。相反,形成列表然后取前 5 个元素。- 我更改了
JOIN
从 开始的顺序team
。您似乎希望每个团队有一行,所以这应该是FROM
.