cocoa mysql_基本MySQL查询

您可以在以下网址尝试查询

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值