SQL by bit 23-t-sql Division

　　

There is no division operation in the T-SQL, but in the T-SQL you can implement a division-like Operation divide. Generally, the result of the division operation is a column from the partitioned relational table, and the rest from the partitioned relational table. Here is an example. Assume that there are three tables as follows: Customer MERs, sales personnel employees, order orders. The query returns some customers and requires these customers and all U.S. employees to have at least one transaction record. Let's look at the following statement:

select custid from Sales.Customers as C
where not exists
(select * from HR.Employees as E
where country='USA'
and not exists
(select * from Sales.Orders as O
where O.custid = C.custid
and O.empid = E.empid))

　　

The statement returns 23 rows indicating that there are 23 customers who have at least one transaction record with each U.S. employee. Now, if we modify the conditions and the requirements are the same, we will change the sales personnel's country to Israel's Israel to see if Israeli sales personnel can be as powerful as American employees.

　　

select custid from Sales.Customers as C
where not exists
(select * from HR.Employees as E
where country='Israel'
and not exists
(select * from Sales.Orders as O
where O.custid = C.custid
and O.empid = E.empid))

　　

Modify the country condition. In this query, we get 91 records. We can see that the customers table has a total of 91 records. Obviously, this result is incorrect. Let's use the statement to see select * from sales. MERs where country like '% Israel %'. The query returns 0 records, that is, there is no Israeli employee at all. Because there are no employees from Israel, all employees and the customer have at least one transaction record. This condition is met for each employee. This is an empty truth in algebra. In other words, each customer has at least one transaction record with this non-existing Israeli employee. This is similar to a division rule:When the divisor is 0, the quotient is infinitely large..

　　
When writing the following statement, we did not consider what to do if the employee table does not have an Israeli employee. If we add an employee from Israel in the problem, we can avoid this error. We only need to limit at least one Israeli employee in the condition to exist in Table employee. This is like a non-zero limit in Division:The divisor is not 0.. The statement is as follows:

　　

select custid from Sales.Customers as C
where not exists
(select * from HR.Employees as E
where country='Israel'
and not exists
(select * from Sales.Orders as O
where O.custid = C.custid
and O.empid = E.empid))
and exists (select * from HR.Employees as E where country='Israel')

Now the query returns 0 results. This 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. Assume that a has attribute a and B has attribute B. In the preceding statement, the relationship to be split is MERs, the divisor relationship is the employee that satisfies a certain relationship, and the intermediary relationship is orders. Here, we use the fourth temporary relationship (select * from HR. Employees as E where country = 'Israel') to avoid the divisor being 0 '). You can also use another method to limit at least one Israeli salesperson and all customers to at least one transaction record. As follows:
A. Find the ID of an American EMPLOYEE: Select empid from HR. Employees where country = 'usa'. This statement finds (, 8 ).
B. Find the number of employees in the United States: Select count (*) from HR. Employees where country = 'usa'. Obviously, the result is 5.
C. Search for the customer ID in the transaction record table based on the employee ID and group the customer ID. Find the number of non-repeated empids in the group that is equal to 5.

　　

select custid
from Sales.Orders
where empid in (1,2,3,4,8) group by custid having count(distinct empid)=5

　　

We restored the Two Queries above. Because there are no employees in Israel, we still use American employees:

select custid
from Sales.Orders
where empid in
(select empid from HR.Employees where country = N'USA')
group by custid
having count(distinct empid) = (select count(*) from HR.Employees where country = N'USA')

The query result is 23 records, which meets our requirements.