基本MySQL查询
基本MySQL查询
提问于 2015-10-01 00:37:17
提供下一个数据库
我需要问几个问题，当我试图：
- 列出所有行星的名单，显示每颗行星上有上尉军衔和战斗次数的士兵。 ID_PLANET，PLANET_NAME，上尉，计数，战斗，计数 选择id_planet，planet_name，count(军衔)，从行星内加入planet_id = id_planet的士兵，其中军衔=‘上尉’；
- 列出了在他们自己星球的战争中，只有的所有士兵名单。 ID_SOLDIER x- NAME_SOLDIER 选择id_soldier，从士兵内部加入planet_id = id_planet的行星名称，在id_planet = id_planet_battle上加入战斗，其中planet_id = id_planet_battle；
- 士兵名单，包括下一个： *姓名 我正在努力。
我的计划是一场灾难，所以经过两天的努力，我来到这里，寻求帮助。
回答 1
Stack Overflow用户
发布于 2015-10-02 03:10:10
您可以在http://sqlfiddle.com/#!9/839d2/1上尝试查询
查询1
列出所有有船长的行星。它显示了id_planet，planet_name，在每一个行星上的总舰长，以及发生在那里的战斗次数(如果有的话)。
SELECT DISTINCT aa.id_planet, aa.planet_name, _aa.captains_count, _bb.battles_count
FROM planet AS aa
INNER JOIN soldier AS bb
ON aa.id_planet = bb.planet_id
INNER JOIN (
SELECT planet_id, COUNT(*) AS captains_count
FROM soldier
WHERE rank = 'captain'
GROUP BY planet_id
) AS _aa
ON aa.id_planet = _aa.planet_id
LEFT JOIN (
SELECT id_planet_battle, COUNT(*) AS battles_count
FROM battle
GROUP BY id_planet_battle
) AS _bb
ON aa.id_planet = _bb.id_planet_battle
WHERE bb.rank = 'captain';您可以使用以下方法获得相同的结果：
SELECT DISTINCT aa.id_planet, aa.planet_name,
(
SELECT COUNT(*)
FROM soldier AS _aa
WHERE _aa.rank = 'captain' AND aa.id_planet = _aa.planet_id
GROUP BY _aa.planet_id
) AS captains_count,
(
SELECT COUNT(*)
FROM battle AS _bb
WHERE aa.id_planet = _bb.id_planet_battle
GROUP BY _bb.id_planet_battle
) AS battles_count
FROM planet AS aa
INNER JOIN soldier AS bb
ON aa.id_planet = bb.planet_id
WHERE bb.rank = 'captain';查询3
SELECT aa.name, aa.rank, bb.planet_name AS planet_from, (
SELECT COUNT(*)
FROM soldier AS _aa
WHERE _aa.planet_id = aa.planet_id
) AS number_of_soldiers,
(
SELECT COUNT(*)
FROM battle AS _bb
WHERE _bb.id_planet_battle = aa.planet_id
) AS number_of_battles
FROM soldier AS aa
INNER JOIN planet AS bb
ON aa.planet_id = bb.id_planet;在这里，我没有像在查询1.1中那样使用Joins计算number_of_soldiers和number_of_battles，因为这将是一个相关的子查询，因此它无法访问外部查询(https://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html)。
错误查询：
SELECT DISTINCT aa.id_planet, aa.planet_name, _aa.captains_count, _bb.battles_count
FROM planet AS aa
INNER JOIN soldier AS bb
ON aa.id_planet = bb.planet_id
INNER JOIN (
SELECT COUNT(*) AS captains_count
FROM soldier AS _aa
WHERE _aa.rank = 'captain' AND aa.id_planet = _aa.planet_id
GROUP BY _aa.planet_id
) AS _aa
ON aa.id_planet = _aa.planet_id
LEFT JOIN (
SELECT COUNT(*) AS battles_count
FROM battle AS _bb
WHERE aa.id_planet = _bb.id_planet_battle
GROUP BY _bb.id_planet_battle
) AS _bb
ON aa.id_planet = _bb.id_planet_battle
WHERE bb.rank = 'captain';因此，上面的查询是错误的，并产生错误：'where子句‘中未知列'aa.id_planet’。
至于您要求的第二个查询，我希望其他人也能尝试一下。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32877745
复制相关文章
相似问题
腾讯云开发者
