mysql - 使用同一表的MySQL子查询

我正在尝试使用子查询，其中==列从同一张表中检索一些ID。

SELECT
`assemblies`.`id`,
`assemblies`.`type`,
`assemblies`.`champion`,
`assemblies`.`name`,
`assemblies`.`author`,
`assemblies`.`githubLastmod`,
(assemblies.forum IS NOT NULL) AS forumExists,
(SELECT GROUP_CONCAT(`champion`) FROM `assemblies` WHERE `githubFolder` = `githubFolder`) AS champions,
(SELECT COUNT(*) FROM `votes` WHERE `assembly` = `assemblies`.`id`) AS votesCount,
(SELECT COUNT(*) FROM `install_clicks` WHERE `assembly` = `assemblies`.`id`) AS installCount,
(SELECT COUNT(*) FROM `github_clicks` WHERE `assembly` = `assemblies`.`id`) AS githubCount,
(SELECT COUNT(*) FROM `forum_clicks` WHERE `assembly` = `assemblies`.`id`) AS forumCount,
(SELECT COUNT(*) FROM `broken_assemblies` WHERE `assembly` = `assemblies`.`id` AND `ip` = '192.168.10.1') AS isBroken,
(SELECT COUNT(*) FROM `votes` WHERE `assembly` = `assemblies`.`id` AND `ip` = '192.168.10.1') AS isUpvoted
FROM
`assemblies`
WHERE
`assemblies`.`type` = 'champion'
    AND `assemblies`.`champion` = '84'
    AND UNIX_TIMESTAMP(DATE(assemblies.githubLastmod)) > '1419617372'
ORDER BY `votesCount` DESC , `githubLastmod` DESC

这部分：

(SELECT GROUP_CONCAT(`champion`) FROM `assemblies` WHERE `githubFolder` = `githubFolder`) AS champions,

这将从所有行返回champion，如果githubAddress匹配，我只想要champion。

（我尝试尽我所能解释这点，如果您需要更多信息，请发表评论）

最佳答案

我们需要GROUP BY githubFolder而不是WHERE githubFolder = githubFolder

如果我们单独执行此命令，它将完全满足我们在此要实现的目标

(SELECT GROUP_CONCAT('champion') FROM assemblies GROUP BY githubFolder) AS champions

但是，如果将其作为子查询执行，则它将返回返回的多行错误

关于mysql - 使用同一表的MySQL子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29286290/