大连企业网站建设公司/互联网营销师证书有用吗

sql除法运算除数为0

The division operator is used when we have to evaluate queries which contain the keyword ALL.

当我们必须评估包含关键字ALL查询时，将使用除法运算符。

Some instances where division operator is used are:

使用除法运算符的一些实例是：

1. Which person has account in all the banks of a particular city?

   哪个人在特定城市的所有银行中都有帐户？

2. Which students have taken all the courses required to graduate?

   哪些学生完成了毕业所需的所有课程？

In above specified problem statements, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units which satisfy these requirements.

在上面指定的问题陈述中，关键字'all'之后的描述定义了一个包含一些元素的集合，最终结果包含了满足这些要求的那些单元。

Another way how you can identify the usage of division operator is by using the logical implication of if...then. In context of the above two examples, we can see that the queries mean that,

确定除法运算符用法的另一种方法是使用if...then的逻辑含义。 在以上两个示例的上下文中，我们可以看到查询的意思是，

1. If there is a bank in that particular city, that person must have an account in that bank.

   如果在该特定城市有银行，则该人必须在该银行中有一个帐户。

2. If there is a course in the list of courses required to be graduated, that person must have taken that course.

   如果要求毕业的课程列表中有一门课程，则该人必须已经修过该课程。

Do not worry if you are not clear with all this new things right away, we will try to expain as we move on with this tutorial.

如果您现在还不清楚所有这些新事物，请不要担心，在继续学习本教程时，我们将尝试进行解释。

We shall see the second example, mentioned above, in detail.

我们将详细看到上面提到的第二个示例。

Table 1: Course_Taken → It consists of the names of Students against the courses that they have taken.

表1：Course_Taken →它由针对所修课程的学生姓名组成。

Table 2: Course_Required → It consists of the courses that one is required to take in order to graduate.

表2：Course_Required →它由一门课程组成，这些课程必须毕业才能毕业。

使用除法运算符 (Using Division Operator)

So now, let's try to find out the correct SQL query for getting results for the first requirement, which is:

因此，现在，让我们尝试找出正确SQL查询以获取第一个需求的结果：

Query: Find all the students who can graduate. (i.e. who have taken all the subjects required for one to graduate.)查询：查找所有可以毕业的学生。 (即已经完成了所有一项毕业所要求的科目。)

Unfortunately, there is no direct way by which we can express the division operator. Let's walk through the steps, to write the query for the division operator.

不幸的是，没有直接的方式可以表达除法运算符。 让我们逐步完成这些步骤，为除法运算符编写查询。

1.找到所有学生 (1. Find all the students)

Create a set of all students that have taken courses. This can be done easily using the following command.

创建一组所有参加过课程的学生。 使用以下命令可以轻松完成此操作。

CREATE TABLE AllStudents AS SELECT DISTINCT Student_Name FROM Course_Taken

This command will return the table AllStudents, as the resultset:

该命令将返回表AllStudents作为结果集：

2.查找所有学生和毕业所需的课程 (2. Find all the students and the courses required to graduate)

Next, we will create a set of students and the courses they need to graduate. We can express this in the form of Cartesian Product of AllStudents and Course_Required using the following command.

接下来，我们将创建一组学生及其毕业所需的课程。 我们可以使用以下命令以AllStudents的笛卡尔积和Course_Required的形式表示。

CREATE table StudentsAndRequired AS 
SELECT AllStudents.Student_Name, Course_Required.Course
FROM AllStudents, Course_Required

Now the new resultset - table StudentsAndRequired will be:

现在，新的结果集-表StudentsAndRequired将是：

3.查找所有未参加的学生和必修课程 (3. Find all the students and the required courses they have not taken)

Here, we are taking our first step for finding the students who cannot graduate. The idea is to simply find the students who have not taken certain courses that are required for graduation and hence they wont be able to graduate. This is simply all those tuples/rows which are present in StudentsAndRequired and not present in Course_Taken.

在这里，我们正在迈出第一步，寻找无法毕业的学生。 这样做的目的是简单地找到尚未参加某些毕业课程的学生，因此他们将无法毕业。 这仅仅是所有那些元组/其存在于StudentsAndRequired和不存在于Course_Taken行。

CREATE  table StudentsAndNotTaken AS 
SELECT * FROM StudentsAndRequired WHERE NOT EXISTS 
(Select * FROM Course_Taken WHERE StudentsAndRequired.Student_Name = Course_Taken.Student_Name 
AND StudentsAndRequired.Course = Course_Taken.Course)

The table StudentsAndNotTaken comes out to be:

表StudentAndNotTaken的结果为：

4.查找所有无法毕业的学生 (4. Find all students who cannot graduate)

All the students who are present in the table StudentsAndNotTaken are the ones who cannot graduate. Therefore, we can find the students who cannot graduate as,

表StudentsAndNotTaken中存在的所有学生都是不能毕业的学生 。 因此，我们可以找到无法毕业的学生，

CREATE table CannotGraduate AS SELECT DISTINCT Student_Name FROM StudentsAndNotTaken

5.查找所有可以毕业的学生 (5. Find all students who can graduate)

The students who can graduate are simply those who are present in AllStudents but not in CannotGraduate. This can be done by the following query:

可以毕业的学生就是那些出现在AllStudents中但不在CannotGraduate中的学生 。 这可以通过以下查询完成：

CREATE Table CanGraduate AS SELECT * FROM AllStudents 
WHERE NOT EXISTS 
(SELECT * FROM CannotGraduate WHERE CannotGraduate.Student_name = AllStudents.Student_name)

The results will be as follows:

结果如下：

Hence we just learned, how different steps can lead us to the final answer. Now let us see how to write all these 5 steps in one single query so that we do not have to create so many tables.

因此，我们刚刚了解到，不同的步骤如何使我们得出最终答案。 现在让我们看看如何在一个查询中编写所有这5个步骤，这样就不必创建太多表了。

SELECT DISTINCT  x.Student_Name FROM Course_Taken AS x WHERE NOT 
EXISTS(SELECT * FROM Course_Required AS y WHERE NOT 
EXISTS(SELECT * FROM Course_Taken AS z WHERE z.Student_name = x.Student_name AND z.Course = y.Course ))

This gives us the same result just like the 5 steps above.

就像上面的5个步骤一样，这给了我们相同的结果。

翻译自: https://www.studytonight.com/dbms/division-operator.php

sql除法运算除数为0