MySQL查询中的GROUP BY子句仅在某些情况下会使我的连接崩溃 [英] The GROUP BY clause in a query in MySQL crashes my connection only in some cases

问题描述

我有2个表，其中之一包含BLOB.

I have 2 tables, one of them contains BLOBs.

psh table
................................
ID
playerID
stateID
sceneID


ps table
..................................
ID
playerID
playerState

我需要获取的是按场景ID(来自psh表)分组的ps表中的ID计数. 因此，我将加入2个表并进行分组. 问题在于此特定查询中的group by子句使数据库崩溃，并且我得到2013错误:查询期间失去连接.

What i need to get is a count of the IDs from ps table grouped by sceneID (from psh table). So I'm joining the 2 tables and grouping. The problem is in this specific query the group by clause crashes the database and i get error 2013: lost connection during query.

如果我尝试使用group by的其他查询，它将很好地工作.

If i try other queries, in which i use group by, it will work just fine.

这是我的查询，请帮忙:

this is my query, please help out:

select count(ps.ID) from innodb.player_spin as ps
JOIN innodb.player_state_history as psh  ON
psh.playerId = ps.playerId
WHERE psh.sceneId IN (9, 19, 29, 30)
group by psh.sceneId;

推荐答案

GROUP BY来自表psh.它应该是驱动表，并与非分组表联接:

The GROUP BY is from table psh. It should be the driving table and join with the non group by table:

select count(ps.ID) 
FROM player_state_history as psh
JOIN player_spin as ps ON
  psh.playerId = ps.playerId
WHERE psh.sceneId IN (9, 19, 29, 30)
GROUP BY psh.sceneId

如果在psh.sceneId

这篇关于MySQL查询中的GROUP BY子句仅在某些情况下会使我的连接崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！