基本的MySQL查询 [英] Basic MySQL Queries

问题描述

给下一个数据库

我需要做几个查询，我遇到了麻烦当我尝试：

1. 列出所有行星的列队，并列出战队队长和战斗次数在每个星球上。

   
   
   

   ID_PLANET | PLANET_NAME | CAPTAINS COUNT | BATTLES COUNT

   
   
   

   选择id_planet，planet_name，count（等级）
   FROM planet
   INNER JOIN士兵ON planet_id = id_planet
   WHERE rank ='Captain';

所有在自己的战争中<只有 的战士名单星球。




ID_SOLDIER | NAME_SOLDIER





SELECT id_soldier，name
FROM soldier
INNER JOIN planet ON planet_id = id_planet
INNER JOIN战斗开启id_planet = id_planet_battle
WHERE planet_id = id_planet_battle;


2. 下一个士兵名单：
   
   

   * NAME | RANK | PLANET_FROM |来自家庭行星的军人人数| BATTLES *

   
   
   

   处理它。

是一个灾难，所以经过两天的尝试，我在这里，寻求帮助。

解决方案

您可以尝试查询
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士兵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 ='队长'; 
  

您可以使用相同的结果：

  SELECT DISTINCT aa.id_planet，aa.planet_name，
（
 SELECT COUNT（*）
 FROM士兵AS _aa 
 WHERE _aa。 rank ='captain'AND aa.id_planet = _aa.planet_id 
 GROUP BY _aa.planet_id 
）AS captains_count，
（
 SELECT COUNT（*）
从战斗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士兵AS bb 
 ON aa.id_planet = bb.planet_id 
 WHERE bb.rank ='队长'; 
  

查询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士兵AS aa 
 INNER JOIN星球AS bb 
 ON aa.planet_id = bb.id_planet; 
  

这里，我没有使用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士兵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'子句'。

至于你询问的第二个查询，我希望别人可以试一试。

giving the next database

I need to make several queries, I'm having troubles when I try to:

1. List of all planets showing the soldiers with a captain rank and the number of the battles in each planet.

   ID_PLANET | PLANET_NAME | CAPTAINS COUNT | BATTLES COUNT

   SELECT id_planet, planet_name , count(rank) FROM planet INNER JOIN soldier ON planet_id = id_planet WHERE rank = 'Captain';

2. List of all soldiers who have been ONLY in a war of their own planet.

   ID_SOLDIER | NAME_SOLDIER

   SELECT id_soldier, name FROM soldier INNER JOIN planet ON planet_id = id_planet INNER JOIN battle ON id_planet = id_planet_battle WHERE planet_id = id_planet_battle;

3. List of soldiers including the next:

   *NAME | RANK | PLANET_FROM | NUMBER OF SOLDIERS FROM HOME PLANET | BATTLES *

   Working on it.

My attemps are a disaster, so after two days trying, here I am, asking for help.

解决方案

You can try the queries at http://sqlfiddle.com/#!9/839d2/1

Query 1

List of all planets that have captains. It shows id_planet, planet_name, total captains on each of those planets as well as the number of battles (if any) that took place there.

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';

You can have same results using this:

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';

Query 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;

Here, i didn't use Joins to calculate number_of_soldiers and number_of_battles as i done at Query 1.1 because that would be a correlated subquery and as such it couldn't have access at the outer queries (https://dev.mysql.com/doc/refman/5.5/en/from-clause-subqueries.html).

Wrong query:

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';

So the above query is wrong and produces the error: Unknown column 'aa.id_planet' in 'where clause'.

As for the second query you asked for i hope someone else could give it a try.

这篇关于基本的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！