Division in the SQL drip 23-t-sql

Text: Division in SQL Drip 23-t-sql

　　

There are no division operations in T-SQL, but operation divide like division can be implemented in T-SQL. The result of a general division operation is that a column is derived from the table that is being removed, and the remainder from the relationship table. Here is an example to illustrate. Let's say there are three tables: Customer customers, sales employees, order orders, queries return some customers and require that these customers and all U.S. employees have at least one transaction record. Consider the following statement:

SelectCustID fromsales.customers asC
where  not exists
(Select *  fromHR. Employees asE
whereCountry='USA'
 and  not exists
(Select *  fromsales.orders asO
whereO.custid=C.custid
 andO.empid=e.empid))

　　

The statement returns 23 rows, indicating that there are 23 customers, who have at least one transaction with each U.S. employee. Now if we change the conditions, the question is still the same, we change the salesperson's country into Israeli Israel to see if Israeli salespeople are as tough as American employees.

　　

SelectCustID fromsales.customers asC
where  not exists
(Select *  fromHR. Employees asE
whereCountry='Israel'
 and  not exists
(Select *  fromsales.orders asO
whereO.custid=C.custid
 andO.empid=e.empid))

　　

Revise the national condition, this time we get the result is 91 records, we see customers This table total also 91 records, obviously this result is wrong. We use the statement to see the select * from sales.customers where country like '%israel% ', the query gets 0 records, that is, there is no Israeli employees. Because there are no employees from Israel, all employees and the customer have at least one transaction record this condition is satisfied for every employee, and this is an empty truth in algebra. In other words, each client has at least one transaction record with the non-existent Israeli employee. This is like a rule of division: The divisor is 0, and the quotient is infinitely large .

　　
When writing the above statement, we did not consider what to do if the employee table did not have any employees in Israel. If we add to the problem the fact that an employee from Israel can avoid this error, it is only necessary to limit the existence of at least one Israeli employee to the table employee in the conditions. This is like a non-0 qualification in division: The divisor is not 0. The statements are as follows:

　　

SelectCustID fromsales.customers asC
where  not exists
(Select *  fromHR. Employees asE
whereCountry='Israel'
 and  not exists
(Select *  fromsales.orders asO
whereO.custid=C.custid
 andO.empid=e.empid))
 and exists(Select *  fromHR. Employees asEwhereCountry='Israel')

Now the query gets 0 results, which is what we want.

　　
There are three relationships in this division operation, a Divide by B Per C,a is the divisor, B is the divisor, and C is the intermediary relationship. Suppose that a has an attribute, a, B has a property. In the above statement is customers, the divisor relationship is the employee who satisfies a certain relationship, the intermediary relationship is orders. Here in order to avoid the problem of divisor is 0, use the fourth temporary relationship (SELECT * from HR. Employees as E where country= ' Israel '). You can also use a different approach to limit at least one Israeli sales person and all customers to have at least once transaction records. As follows:
A, find Id:select empid from HR for US employees. Employees where country= ' USA ', this statement found is (1,2,3,4,8).
b, find the number of U.S. employees: Select COUNT (*) from HR. Employees where country= ' USA ', it is clear that the result of this finding is 5.
C, find the customer ID in the transaction table based on the employee ID and group by Customer ID, and find the number of Empid in the group equal to 5.

　　

SelectCustID
 fromsales.orders
whereEmpidinch(1,2,3,4,8) Group  byCustID having Count(distinctempid)=5

　　

We restore the above two queries, because there are no Israeli employees, or use American employees:

SelectCustID
 fromsales.orders
whereEmpidinch
(SelectEmpid fromHR. EmployeeswhereCountry=N'USA')
Group  byCustID
 having Count(distinctempid)= (Select Count(*)  fromHR. EmployeeswhereCountry=N'USA')

The result of the query is 23 records that meet our requirements.

　

Division in the SQL drip 23-t-sql