根据多个表中的单列或多列查找重复记录的计数和列表

Question

我在ActiveSales和PastSales下面有两个表。我想从每个商品的两个表中查找重复的实例，并从两个表中获取每个商品ID的相同ItemID的售出多个实例的计数。

要求:1、2、3个实例可以销售相同的ItemID，不限销售日期，但SaleInstances不能重复。

ActiveSales:
ItemID SaleInstance  SoldDate
10001  1             18-May-2020
10002  1              5-May-2020
10003  2             20-May-2020 



PastSales:

ItemID SaleInstance  SoldDate
13401  2             12-March-2020
10002  1              5-April-2020
10003  1             22-April-2020 
10002  2              15-April-2020
10001  3              2-April-2020 
10003  1             2-Feb-2020 
10002  1              5-March-2020
10001  3             8-April-2020  
10001  1              2-Jan-2020

预期结果：

1) Count of Duplicate sale at instance-1 for ItemID - 10001 : 2
   Count of Duplicate sale at instance-2 for ItemID - 10001 : 0
   Count of Duplicate sale at instance-3 for ItemID - 10001 : 2
   Count of Duplicate sale at instance-1 for ItemID - 10002 : 3
   Count of Duplicate sale at instance-2 for ItemID - 10002 : 0
   Count of Duplicate sale at instance-3 for ItemID - 10002 : 0
   Count of Duplicate sale at instance-1 for ItemID - 10003 : 2
   Count of Duplicate sale at instance-2 for ItemID - 10003 : 0
   ..
   ..
   ..

2)   Complete List:

ItemID SaleInstance   SoldDate
10001  1              2-Jan-2020
10001  1             18-May-2020
10001  3              2-April-2020
10001  3             8-April-2020 
10002  1              5-March-2020
10002  1              5-April-2020
10002  1              5-May-2020
10002  2              15-April-2020
10003  1             2-Feb-2020
10003  1             22-April-2020
10003  2             20-May-2020 
13401  2             12-March-2020

Answer 1

使用HAVING子句仅显示count >1的行

select ItemID, SaleInstance 
  from (select * from ActiveSales union all select * from PastSales) t 
group by ItemID, SaleInstance 
  having count(*) > 1

致敬Olafur