使用分组依据，等级，行编号

我有两个桌子。一个是CustomerOrders，另一个是OrderCustomerRef-查找表。

两个表都具有一对多关系-一个客户可能与多个订单相关联。

CustomerOrders表具有重复的客户（相同的LName，FName，Email）。但是它们具有不同的Cust_ID。

我需要合并基本客户表中的所有重复联系人（一对一）。 （此表未在此处显示）。

第1步：

需要找出应将哪个Cust_ID合并到相应的重复客户（相同的LName，FName，Email）中。具有最新Order_Date的联系人应赢得其对应的重复副本（客户）。 VIP客户有一个例外-无论Order_Date如何，他们都应该一直是获胜者。

步骤2：
更新了OrderCustomerRef表：将所有丢失的重复Cust_ID替换为获胜的Cust_ID。

第3步：
从基本“客户”表中删除所有丢失的联系人（在当前范围内为否。我自己做）。

IF OBJECT_ID('tempdb..#table') IS NOT NULL
DROP TABLE #table;
IF OBJECT_ID('tempdb..#CustomerOrders') IS NOT NULL
DROP TABLE #CustomerOrders;
IF OBJECT_ID('tempdb..#OrderCustomerRef') IS NOT NULL
DROP TABLE #OrderCustomerRef;
CREATE TABLE #CustomerOrders
(
[PK_ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),Cust_ID INT NOT NULL,LName VARCHAR(100) NULL,FName VARCHAR(100) NULL,[Customer_E-mail] VARCHAR(100) NULL,Order_Date DATETIME NULL,Customer_Source VARCHAR(100) NULL,CustomerType VARCHAR(100) NULL
)
INSERT INTO #CustomerOrders (Cust_ID,LName,FName,[Customer_E-mail],Order_Date,Customer_Source,CustomerType)
VALUES
(1,'John','Smith','JSmith@email.com','2018-11-10 01:40:55.150','XYZ Company','Regular'),(2,'2018-10-10 05:05:55.150','Internet','VIP'),(3,'Adam','Burns','ABurns@email.com','2017-05-05 00:00:00.000',(4,(5,(6,'James','snatcher','Jsnatcher@email.com','2019-07-07 00:00:00.000',(7,(9,'Thomas','Johnson','TJohnson@email.com','2016-05-01 00:00:00.000','2015-04-01 00:00:00.000',(10,'2014-03-01 00:00:00.000',(11,'2013-02-01 00:00:00.000',(12,'Peter','McDonald','PMcDonald@email.com',(13,'Jose','Mainster','JMainster@email.com',(14,'Kevin','Digginton','KDigginton@email.com','2015-09-03 00:00:00.000','Regular')
CREATE TABLE #OrderCustomerRef
(
Raw_PK INT NOT NULL PRIMARY KEY IDENTITY(1,OrderID INT NOT NULL,Cust_ID INT NULL,OrderType VARCHAR(100) NULL
)
INSERT INTO #OrderCustomerRef (OrderID,Cust_ID,OrderType)
VALUES
(1,1,'Online'),2,3,4,'In Store'),5,6,(8,7,9,10,11,12,13,(15,14,(16,'In Store')
-- SELECT * FROM #OrderCustomerRef
SELECT *,RANK() OVER (PARTITION BY FName,Customer_Source ORDER BY Order_Date DESC) AS Rank_1,Customer_Source ORDER BY Order_Date,CustomerType DESC ) AS Rank_CustType,RANK() OVER (PARTITION BY Cust_ID,CustomerType DESC ) AS Rank_CustID,[Customer_E-mail] ORDER BY Order_Date DESC) AS Rank_2,[Customer_E-mail] ORDER BY Cust_ID) AS Rank_3
FROM #CustomerOrders


所需的输出看起来像：

*例外：
-丢失客户ID 1、3（应该赢了，但是由于副本重复，因此是VIP，所以正在丢失）
-赢得客户ID 2、5（因为它是VIP，有例外）

例如：## OrderCustomerRef中所有Cust_ID为 1 的John Smith Cust_ID都应替换为Cust_ID为 2 的John Smith。应该将Cust_ID为3的Adam Burns的Cust_ID替换为Cust_ID为5的Adam Burns。

一般规则：
-丢失客户ID ，7、10、11、4
-赢得客户ID ，6、9、12、13、14

例如：## OrderCustomerRef中所有出现的Cust_ID为7都应替换为6，所有出现的Cust_ID为10都应替换为9 *

最终，我应该在## OrderCustomerRef表中仅拥有客户ID 6、9、12、13、14、2、5。

使用Rank_CustType_1，column_1，column_2，我可以弄清楚步骤1。
但是我仍然对第2步有问题-像这样更新OrderCustomerRef表：应将所有丢失的Cust_ID替换为对应的重复的获胜Cust_ID。

我已经尝试过了。但这仍然不能代替丢失的Cust_ID。

SELECT *,[Customer_E-mail] ORDER BY Order_Date,CustomerType DESC) AS Rank_CustType_1,[Customer_E-mail] ORDER BY Cust_ID) AS Rank_3
INTO #table
FROM #CustomerOrders
; with cte as (
select Cust_ID,max(t.Rank_CustType_1) as Rank_CustType_1,(select distinct Cust_ID from #table a where a.Cust_ID = t.Cust_ID and Rank_3 = 1) column_1,(select distinct Cust_ID from #table a where a.Cust_ID = t.Cust_ID and Rank_3 <> 1) column_2
from #table t
group by Cust_ID,[Customer_E-mail]
)
update b
set Cust_ID = case
when b.Cust_ID = cte.Cust_ID and
b.Cust_ID = IsnULL(cte.column_1,'') and Rank_CustType_1 != 1 then b.Cust_ID
when b.Cust_ID = cte.Cust_ID and
b.Cust_ID = IsnULL(cte.column_2,'') and Rank_CustType_1 != 1 then cte.column_2
when b.Cust_ID = cte.Cust_ID and Rank_CustType_1 = 1 and cte.column_1 is null and cte.column_2 is not null then cte.column_2
when b.Cust_ID = cte.Cust_ID and Rank_CustType_1 = 1 and cte.column_1 is not null and cte.column_2 is null then cte.column_1
end
from #OrderCustomerRef b
inner join cte on b.Cust_ID = cte.Cust_ID;
select * from #OrderCustomerRef;


根据您提供的信息，我使用了以下CTE来显示可以得到所需内容的结果：

WITH DaCTE -- To rank the existing rows
AS (
SELECT pk_ID,cust_ID,fname,lname,[customer_e-mail],Order_Date,Customer_Source,customertype,ROW_NUMBER() OVER (PARTITION BY fname,[customer_e-mail] ORDER BY customertype DESC,order_date DESC,cust_id) as RankYo -- Orders by the criteria provided but while you suggested 3 should lose to 5,they have the same criteria so either one could win based on ordering
FROM #customerorders
),NewSource -- To show winning Customer ID next to Original ID
AS (
SELECT co.pk_ID,DaCTE.cust_ID as NewCustomerID,co.cust_ID as OriginalCustomerID,co.fname,co.lname,co.[customer_e-mail],co.Order_Date,co.Customer_Source,co.customertype
FROM DaCTE
INNER JOIN #CustomerOrders as co
ON co.fname = DaCTE.FName
AND co.lname = DaCTE.LName
AND co.[customer_e-mail] = DaCTE.[Customer_E-mail]
WHERE DaCTE.RankYo = 1 -- filter to show only the winning IDs based on resulting rank from previous CTE
)
SELECT *
/*UPDATE ocr --commented out so you can see the results before running update
SET ocr.Cust_ID = ns.NewCustomerID*/
FROM #OrderCustomerRef as ocr
INNER JOIN NewSource as ns
ON ns.OriginalCustomerID = ocr.Cust_ID