SQL點滴23—T-SQL中的除法

在T-SQL中沒有除法運算，但是在T-SQL中可以實現類似除法的操作Divide。一般除法操作的結果一個列來自于被除關系表，剩下的來自除關系表。這里舉一個例子來說明。假設如下有三個表：客戶Customers，銷售人員Employees，訂單Orders，查詢返回一些客戶，要求這些客戶和所有美國雇員都至少有一次交易記錄。來看下面一個語句：

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))

語句返回23行，標明有23名客戶，這些客戶至少和每個美國雇員有一次交易記錄。現在我們如果修改一下條件，問題要求還是一樣的，我們把銷售人員的國家修改成以色列Israel，看看以色列的銷售人員是否能像美國雇員一樣的強悍。

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))

修改國家條件，這次我們查詢得到的結果是91條記錄，我們看看Customers這個表總共也就91條記錄，很明顯這個結果不對。我們使用語句來看看select * from Sales.Customers where country like '%Israel%'，查詢得到0條記錄，就是說根本就沒有以色列的雇員。因為沒有來自以色列的雇員，所有雇員和該客戶擁有至少一項交易記錄這個條件對每個雇員都滿足，這個是代數里面的空真現象。換句話說每個客戶都和這個不存在的以色列雇員至少有一項交易記錄。這個很像除法運算的一個規則：除數是0，商就是無限大。

　　
寫上面的語句的時候，我們沒有考慮如果Employee表中沒有如果沒有以色列的雇員怎么辦。如果我們在問題中加上確實存在來自以色列的雇員就可以避免這個錯誤，只需要在條件中限定至少有一個以色列雇員存在于表Employee中就可以了。這個就像除法中的非0限定：除數不為0。語句如下：

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')

現在查詢得到0條結果，這才是我們想要的。

　　
在這個除法操作中有三個關系，a Divide by b Per c，a是被除數，b是除數，c是中介關系。假設a有屬性A，b有屬性B。在上面的語句中被除關系是Customers，除數關系是滿足一定關系的Employee，中介關系是Orders。這里為了避免除數是0 的問題，使用第四個臨時關系(select * from HR.Employees as E where country='Israel')。也可以使用另外一種方法來限定至少有一名以色列的銷售人員和所有顧客至少有一次交易記錄。如下：
a，找到美國雇員的id：select empid from HR.Employees where country='USA'，這個語句找到的是(1,2,3,4,8)。
b，找到美國雇員的數量：select COUNT(*) from HR.Employees where country='USA'，很明顯這個找到的結果是5。
c，根據雇員id查找交易記錄表中的客戶id，并按客戶id分組，在分組中查找不重復的empid數目等于5的。

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

我們把上面兩個查詢還原上去，由于沒有以色列的雇員，還是使用美國雇員：

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')

查詢得到的結果是23條記錄，符合我們的要求。