如何过滤表以仅检索每个记录的一次出现

Question

我试图为每个客户只找到一次出现的情况。

但是，在我的数据库中，我的客户已添加两次（在 ERP 迁移之后）。

目前，

如果我尝试查找出现两次的客户，我必须保留“customer_id”中具有“C”的客户 在此示例

中，“Manu Johns”出现了 2 次，因此我们必须保留最终表中 customer_id 列中具有“C”的那个。

如果我只找到该客户的一次出现。但是，customer_id 列中没有“C”。我们必须按原样添加到最终表中

在这个示例中，我们有“Mathieu Wainers”，只有当我们将其保留在最终表中时，

它才会出现哪个查询将允许我得到以下结果： https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9484f43c0a6c1ccdae7d659ca53e1eab

CREATE TABLE PersonsInitial (
    tel int,
    firstname varchar(255),
    lastname varchar(255),
    Customer_ID varchar(255)
);
insert into PersonsInitial(tel,firstname,lastname,Customer_ID) values
('01234','Manu','Johns','456'),
('01234','Manu','Johns','C456'),
('21234','Fernand','Wajk','C389'),
('13554','Mathieu','Wainers','4683');

select distinct tel, firstname, lastname, customer_id from PersonsInitial

--if there is a person with the same tel number chose the customer id with 'C'
--if I don't have the choice add the customer without C

CREATE TABLE PersonsFinal (
    tel int,
    firstname varchar(255),
    lastname varchar(255),
    Customer_ID varchar(255)
);
insert into PersonsFinal(tel,firstname,lastname,Customer_ID) values
('01234','Manu','Johns','C456'),
('21234','Fernand','Wajk','C389'),
('13554','Mathieu','Wainers','4683');

select distinct tel, firstname, lastname, customer_id from PersonsFinal

Answer 1

您可以根据客户 ID 中是否有“C”将它们排名第一。这就是 cte 存在的原因。

with cte as (select row_number() over (partition by tel, firstname, lastname order by case when left(customer_id, 1) = 'C' then 0 else 1 end) rn, 
                    p.* 
               from PersonsInitial p)
select *
  from cte 
 where rn = 1; <-- selects only those with "C" or those for that there is no "C" lines

dbfiddle

Answer 2

这个问题有多种解决方案。例如，您可以使用 OUTER APPLY。即：

insert into PersonsFinal(tel,firstname,lastname,Customer_ID) 
select distinct pi1.tel, pi1.firstname, pi1.lastname, coalesce(pi.Customer_ID, pi1.Customer_ID) Customer_Id 
from PersonsInitial pi1
outer apply (select top(1) * 
     from PersonsInitial pi2 
     where pi1.tel = pi2.tel 
         and pi1.firstname = pi2.firstname 
         and pi1.lastname = pi2.lastname
         and pi2.Customer_ID like 'C%') pi;

DBFiddle 演示

Answer 3

另一个解决方案：

WITH CTE AS
(
SELECT tel, 
       firstname, 
       lastname, 
       Customer_ID, 
       ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY COL_LENGTH('PersonsInitial','Customer_ID') DESC) AS RowNumber 
FROM   PersonsInitial
)

SELECT tel, 
       firstname, 
       lastname, 
       Customer_ID
FROM CTE 
WHERE RowNumber = 1