我試圖為每個客戶只找到一次。
但是，在我的資料庫中，我有兩次添加的客戶（在 ERP 遷移之后）
現在，
如果我嘗試查找出現兩次的客戶，我必須在“customer_id”列中保留具有“C”的客戶
在此示例中，我們有出現 2x 的“Manu Johns”，因此我們必須保留在最終表的 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
uj5u.com熱心網友回復：
您可以根據客戶 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
小提琴手
uj5u.com熱心網友回復：
這個問題有多種解決方案。例如，您可以使用 OUTER APPLY。IE：
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 演示
uj5u.com熱心網友回復：
另一個解決方案：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/431098.html