基本MySQL查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了基本MySQL查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2926字，纯文字阅读大概需要5分钟。

内容图文

提供下一个数据库

我需要进行几个查询,尝试以下操作时遇到麻烦：

>所有行星的清单,显示具有船长级的士兵以及每个行星的战斗次数.

ID_PLANET | PLANET_NAME | CAPTAINS COUNT |电池数

选择id_planet,planet_name,count(rank)
来自星球
内战士兵加入planet_id = id_planet
WHERE rank =’Captain’;
>仅在自己星球上进行过战争的所有士兵名单.

ID_SOLDIER | NAME_SOLDIER

SELECT id_soldier,名称
从士兵
内联接行星上planet_id = id_planet
参加ID_planet = id_planet_battle的内战
在哪里planet_id = id_planet_battle;
>士兵名单,包括下一个：

* NAME |排名| PLANET_FROM |本地士兵的数量|战斗*

正在努力.

我的尝试是一场灾难,所以经过两天的尝试,我在这里寻求帮助.

解决方法:

您可以在以下网址尝试查询
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’.

至于您要求的第二个查询,我希望其他人可以尝试一下.

内容总结

以上是互联网集市为您收集整理的基本MySQL查询全部内容，希望文章能够帮你解决基本MySQL查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端