您可以在以下網址嘗試查詢

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’.

至于您要求的第二個查詢,我希望其他人可以嘗試一下.

總結

以上是生活随笔為你收集整理的cocoa mysql_基本MySQL查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。