sql - 如何在SQL中的某些列上找到相似的数据

Question

我试图用两列(Item, Customer)获取类似的数据，根据下面的查询，我没有得到重复数据的第一个值。

请帮助我获得如下所示的预期结果。

样本数据

+------+------------+-----------+----------+
| E_ID |   E_Date   |   Item    | Customer |
+------+------------+-----------+----------+
|    1 | 10/10/2017 | Microsoft | DIB      |
|    2 | 10/11/2017 | Oracle    | UNB      |
|    3 | 10/12/2017 | IBM       | UNB      |
|    4 | 10/13/2017 | Microsoft | DIB      |
|    5 | 10/14/2017 | Oracle    | UNB      |
|    6 | 10/15/2017 | SAP       | DIB      |
|    7 | 10/15/2017 | Microsoft | DIB      |
+------+------------+-----------+----------+

我的脚本

SELECT * FROM
(
    SELECT E_ID, E_Date,Item,Customer,
         Row_Number() OVER(PARTITION BY  Customer,Item ORDER By E_ID)AS Rank 
         FROM Events        
) AS B WHERE Rank > 1

期望结果是

+------+------------+----------+----------+
| E_ID |   E_Date   |   Item   | Customer |
+------+------------+----------+----------+
|    1 | 10/10/2017 | Mirosoft | DIB      |
|    4 | 10/13/2017 | Mirosoft | DIB      |
|    7 | 10/15/2017 | Mirosoft | DIB      |
|    2 | 10/11/2017 | Oracle   | UNB      |
|    5 | 10/14/2017 | Oracle   | UNB      |
+------+------------+----------+----------+

我的实际结果

+------+------------+-----------+----------+------+
| E_ID |   E_Date   |   Item    | Customer | Rank |
+------+------------+-----------+----------+------+
|    4 | 10/13/2017 | Microsoft | DIB      |    2 |
|    7 | 10/15/2017 | Microsoft | DIB      |    3 |
|    5 | 10/14/2017 | Oracle    | UNB      |    2 |
+------+------------+-----------+----------+------+

Answer 1

如果我没看错，您应该使用 COUNT 而不是 ROW_NUMBER :

SELECT * FROM
(
    SELECT E_ID, E_Date, Item, Customer,
        COUNT(*) OVER (PARTITION BY Customer, Item) AS cnt
    FROM Events        
) t
WHERE cnt > 1
ORDER BY Item, Customer;

上述查询将返回每个客户/项目组的记录，该组中有多个记录。

Demo