学习SQL ...是否有更好的方法编写此代码？ [英] Learning SQL ... Is there a better way to write this?

问题描述

这是我要回答的问题...

This is the question I'm trying to answer...

在$ b中出现只有一名球员的所有球队的队名是什么$ b 145个或更多游戏？

What are the team names of all teams who had one and only one player appear in 145 or more games?

这是我的解决方案。

SELECT name
  From Teams
 WHERE teamID IN (SELECT original.teamID
                    FROM Appearances original
                   WHERE teamID Not In (SELECT one.teamID
                                         FROM Appearances one, Appearances two
                                        Where (one.teamID = two.teamID) 
                                          AND (one.playerID <> two.playerID) 
                                          AND (one.GS > 144) AND (two.GS > 144)));

这行得通，但我想知道是否有更干净/更有效的方式编写此代码。我正在使用Derby作为我的dbms。

This works, but I'm wondering if there is a cleaner/more efficient way to write this. I'm using Derby as my dbms.

推荐答案

我不确定您的查询是否有效。我希望它更像这样：

I'm not sure if your query does work. I would would expect it to be more like this:

SELECT name
  FROM Teams
 WHERE teamID IN 
       ( SELECT one.teamID
           FROM Appearances one
          WHERE (one.GS > 144)
                AND one.teamID NOT IN 
                ( SELECT two.teamID
                    FROM Appearances two
                   WHERE (one.teamID = two.teamID)
                         AND (one.playerID <> two.playerID) 
                         AND (two.GS > 144) ) );

这篇关于学习SQL ...是否有更好的方法编写此代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！