使用相同表的MySQL子查询

Question

我试图使用列==列所在的子查询从同一个表中检索一些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，如果champion匹配，则只需要githubAddress。

(我尽我所能向你解释，如果你需要更多的信息，请评论)

Answer 1

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

如果我们单独执行，它将完全符合我们在这里想要实现的目标。

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

但是，如果这将作为子查询执行，则会在返回多行时抛出错误。