我有两张桌子。
FootballPlayers
包含 Id_footballplayer、Last_Name、Fisrt_Name、Age 列 Transfers
包含 Id_transfer、Name_club、价格、日期、接受(是或否)、code_footballplayer 列如何编写 SQL 查询来选择玩家的姓氏以及他们进行的成功转会总数超过 3 的总和?
我已经写了一个查询,显示每个玩家所有成功转移的总金额
SELECT FootballPLayers.Last_Name,
SUM(CASE acceptance WHEN 'yes' THEN price ELSE 0 END) AS amount_price
FROM FootballPlayers
INNER JOIN Transfers ON FootballPlayers.ID_footballplayer = Transfers.code_footballplayer
GROUP BY FootballPlayers.Last_Name;
但是我不知道如果成功转移的次数超过3,如何添加条件
由于这是一个组场景,因此GROUP BY
您可能需要:
HAVING COUNT(1) > 3
该HAVING
子句的工作方式与 非常相似WHERE
,但应用方式不同。
另一种方法是子查询:
SELECT * FROM
(
SELECT FootballPLayers.Last_Name,
SUM(CASE acceptance WHEN 'yes' THEN price ELSE 0 END) AS amount_price,
COUNT(1) AS [Transfers]
FROM FootballPlayers
INNER JOIN Transfers ON FootballPlayers.ID_footballplayer = Transfers.code_footballplayer
GROUP BY FootballPlayers.Last_Name
) x
WHERE x.Transfers > 3
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句