在SQL查询中使用any / all

在SQL查询中使用any / all - 似乎不适用于我尝试过的任何应用程序(Using any / all in an SQL query - doesn't seem to work in any app I've tried)

我已经尝试过SQLiteSpy，SQLite Administrator和SQLite Expert Personal，但这些关键字似乎都没有。 我正在关注Coursera上的数据库简介，教授正在使用Database Master进行查询。 我无法使用该应用程序，因为我不断收到路径中非法字符的错误，所以我放弃了它。 此外，它不是一个免费的应用程序。

在所有情况下，下面的代码块都会在“select”周围出错。 如果我删除关键字，查询将运行但返回错误的结果。 我不确定为什么它不起作用。 谁知道？

无论如何，这是代码的一个例子......

select cName from College C1 where enrollment > all ( select enrollment from College C2 where C2.cName <> C1.cName);

使用全部的另一个例子

select College.cName, state, GPA from College, Apply, Student where College.cName = Apply.cName and Apply.sID = Student.sID and GPA >= all (select GPA from Student, Apply where Student.sID = Apply.sID and Apply.cName = College.cName);

最后一个例子使用任何......

select cName from College S1 where not enrollment <= any (select enrollment from College S2 where S2.cName <> S1.cName);

I've tried SQLiteSpy, SQLite Administrator and SQLite Expert Personal and none of the apps seem to work with these keywords. I'm following along the Intro to Databases on Coursera and the professor is using Database Master for querying. I'm unable to use the app as I keep getting errors about illegal characters in the path so I gave up on it. Plus it's not a free app.

In all cases, the code blocks below will error out around "select". If I remove the keywords, the query will run but return the wrong results. I'm not sure why it isn't working. Anyone know?

Anyway, this is an example of code...

select cName from College C1 where enrollment > all ( select enrollment from College C2 where C2.cName <> C1.cName);

Another example using all...

select College.cName, state, GPA from College, Apply, Student where College.cName = Apply.cName and Apply.sID = Student.sID and GPA >= all (select GPA from Student, Apply where Student.sID = Apply.sID and Apply.cName = College.cName);

And a final example using any...

select cName from College S1 where not enrollment <= any (select enrollment from College S2 where S2.cName <> S1.cName);

最满意答案

我没有发现any和all真正有用的理解查询。 您可以将查询重写为：

select cName from College C1 where enrollment > ( select max(enrollment) from College C2 where C2.cName <> C1.cName); select College.cName, state, GPA from College join Apply on College.cName = Apply.cName join Student on Apply.sID = Student.sID where GPA >= (select max(GPA) from Student join Apply on Student.sID = Apply.sID where Apply.cName = College.cName );

最后一点有点难以理解。 我觉得是这样的：

select cName from College S1 where enrollment > (select max(enrollment) from College S2 where S2.cName <> S1.cName);

作为奖励，我还将查询更改为使用显式join语法而不是where子句中的隐式连接。

I don't find any and all to really be useful in understanding queries. You can rewrite your queries as:

select cName from College C1 where enrollment > ( select max(enrollment) from College C2 where C2.cName <> C1.cName); select College.cName, state, GPA from College join Apply on College.cName = Apply.cName join Student on Apply.sID = Student.sID where GPA >= (select max(GPA) from Student join Apply on Student.sID = Apply.sID where Apply.cName = College.cName );

The last one is a bit harder to understand. I think it is:

select cName from College S1 where enrollment > (select max(enrollment) from College S2 where S2.cName <> S1.cName);

As a bonus, I also changed the queries to use explicit join syntax rather than implicit joins in the where clause.