在上一篇文章里，我討論了INTERSECT設置操作的基礎，它和INNER JOIN的區(qū)別，還有為什么需要好的索引設計支持。今天我想談下SQL Server里并未實現(xiàn)的INTERSECT ALL操作。

INTERSECT ALL是SQL特性的一部分，但SQL Server并不考慮它。和INTERSECT操作的區(qū)別非常簡單：INTERSECT ALL不會剔除重復行。在SQL Server里的好處是你可以模擬INTERSECT ALL。我們來試下，再次創(chuàng)建2個表，并插入一些行。

 1 -- Create the 1st table 2 CREATE TABLE t1 3 ( 4     Col1 INT, 5     Col2 INT, 6     Col3 INT 7 ) 8 GO 9 10 -- Create the 2nd table11 CREATE TABLE t212 (13     Col1 INT,14     Col2 INT15 )16 GO17 18 -- Insert some records into both tables19 INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (2, 2, 2), (3, 3, 3)20 INSERT INTO t2 VALUES (2, 2), (2, 2), (3, 3)21 GO

你會發(fā)現(xiàn)，第2個表包含重復記錄——在表里值為2的記錄出現(xiàn)了2次。現(xiàn)在當你在2個表之間進行INTERSECT，值為2的記錄在結果集只出現(xiàn)1次。重復行被剔除了。

如果你想保留重復行，你必須使它們唯一。這里的一個方法是使用自SQL Server 2005后引入的ROW_NUMBER()窗口函數(shù)。使用這個函數(shù)你為每個重復記錄生成唯一的行號。因此你的重復記錄變成了唯一，“重復”行如期望的返回2次。下列代碼顯示了這個技術：

 1 -- You can PReserve duplicate rows by making them unique with the ROW_NUNBER() Windowing Function. 2 WITH IntersectAll AS 3 ( 4     SELECT 5         ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY (SELECT 0)) AS RowNumber, 6         Col1, 7         Col2 8     FROM t1 9 10     INTERSECT11 12     SELECT13         ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY (SELECT 0)) AS RowNumber,14         Col1,15         Col216     FROM t217 )18 SELECT Col1, Col2 FROM IntersectAll19 GO

SQL Server里INTERSECT操作的一個副作用是重復行會剔除不會在結果集里返回。如果你想保留它們，你需要使它們唯一，例如應用ROW_NUMBER() 計算。

感謝關注！