Je ne peux pas exprimer correctement le problème pour trouver la réponse en ligne, donc j'espère que quelqu'un pourra me fournir un lien vers une solution car je pense que c'est une tâche assez courante.
Nous avons une hiérarchie de produits et de souhaits pour déterminer les parents. Tous les noms de produits sont dans la même colonne et la logique est la suivante
ProductId ProductName ParentName ParentId ------------------------------------------------------------------------------ 1 ABC NULL NULL 2 ABCD ABC 1 3 ABCD1 ABCD 2 ...
Le résultat doit être
ABCD1 & ABCD2 sont enfants de ABCD et ABCD est l'enfant de ABC
ProductId ProductName ----------------------------------------------------------------------------- 1 ABC 2 ABCD 3 ABCD1 4 ABCD2
5 Réponses :
Hmmm. Je pense que cela fait ce que vous voulez:
select p.*, pp.ProductName as parentName, pp.ProductId as parentId
from products p outer apply
(select top (1) pp.*
from products pp
where p.ProductName like pp.ProductName + '%' and
p.ProductId <> pp.ProductId
order by len(pp.ProductName) desc
) pp;
J'ai fait un CTE pour ma table (~ 200k lignes) et cela fonctionne parfaitement. Merci beaucoup.
Avez-vous essayé d'utiliser Case avec la condition et de représenter chaque condition comme une nouvelle colonne. Vous pouvez vous référer à la syntaxe https://www.w3schools.com/sql/sql_case.asp < / a>
S'il n'y a qu'une seule différence de caractère.
Ensuite, vous pouvez LEFT JOIN au ProductName et à un caractère générique '_'
SELECT TOP (1) WITH TIES p1.ProductId, p1.ProductName, p2.ProductName AS ParentName, p2.ProductId AS ParentId FROM Products p1 LEFT JOIN Products p2 ON p1.ProductName LIKE CONCAT(p2.ProductName,'_%') ORDER BY ROW_NUMBER() OVER (PARTITION BY p1.ProductId ORDER BY LEN(p2.ProductName) DESC);
Exemple d'extrait:
ProductId ProductName ParentName ParentId 1 ABC NULL NULL 2 ABCD ABC 1 3 ABCD1 ABCD 2 4 ABCD2 ABCD 2
Résultat:
declare @Products table (
ProductId INT primary key identity(1,1),
ProductName varchar(30) not null,
unique (ProductName)
);
insert into @Products (ProductName) values
('ABC')
,('ABCD')
,('ABCD1')
,('ABCD2')
;
SELECT
p1.ProductId,
p1.ProductName,
p2.ProductName AS ParentName,
p2.ProductId AS ParentId
FROM @Products p1
LEFT JOIN @Products p2 ON p1.ProductName LIKE CONCAT(p2.ProductName,'_')
ORDER BY p1.ProductId;
S'il est possible qu'il y ait plus d'un caractère de différence, alors:
SELECT p1.ProductId, p1.ProductName, p2.ProductName AS ParentName, p2.ProductId AS ParentId FROM Products p1 LEFT JOIN Products p2 ON p1.ProductName LIKE CONCAT(p2.ProductName,'_') ORDER BY p1.ProductId;
La différence de caractère peut varier entre 1 et 34
Vous pouvez utiliser l'expression de table commune (CTE) pour faire le travail.
with product_result (ProductId, ProductName, ParentName, ParentId) as
(
select ProductId, ProductName, convert(varchar,null) ParentName, convert(int, null) ParentId
from <YOUR_PRODUCT_TABLE_GOES_HERE>
where ProductName = 'ABC' --start with
union all
select d.ProductId, d.ProductName, convert(varchar,p.ProductName) ParentName, p.ProductId ParentId
from <YOUR_PRODUCT_TABLE_GOES_HERE> d
, product_result p
where d.ProductName like p.ProductName+'_'
)
select *
from product_result
La première partie table_produit doit être remplacée par votre propre table de produits. Il est utilisé ici pour générer un ensemble de données temporaire.
Votre requête finale ressemblera à:
with product_table (ProductId, ProductName) as
(
select 1 ProductId , 'ABC' ProductName union all
select 2 ProductId , 'ABCD' ProductName union all
select 3 ProductId , 'ABCD1' ProductName union all
select 4 ProductId , 'ABCD2' ProductName --union all
)
,product_result (ProductId, ProductName, ParentName, ParentId) as
(
select ProductId, ProductName, convert(varchar,null) ParentName, convert(int, null) ParentId
from product_table
where ProductName = 'ABC' --start with
union all
select d.ProductId, d.ProductName, convert(varchar,p.ProductName) ParentName, p.ProductId ParentId
from product_table d
, product_result p
where d.ProductName like p.ProductName+'_'
)
select *
from product_result
CTE est disponible depuis SQL2008. pour plus d'informations WITH common_table_expression (Transact-SQL)
Il ne fait aucun doute que la réponse de Gordon est la meilleure ici, mais j'ai quand même essayé:
USE TEMPDB
CREATE TABLE #T (ProductID INT, ProductName VARCHAR (100))
INSERT INTO #T VALUES (1, 'ABC'), (2, 'ABCD'), (3, 'ABCD1'), (4, 'ABCD2')
WITH CTE AS
(
SELECT T.*,
T2.ProductID AS ParentID,
T2.ProductName AS ParentName
FROM #T AS T
CROSS JOIN #T AS T2
WHERE T.ProductName LIKE T2.ProductName + '%'
AND T.ProductID <> T2.ProductID
)
, CTE2 AS
(
SELECT TOP 1 T.*,
NULL AS ParentID,
NULL AS ParentName
FROM #T AS T
ORDER BY LEN (T.ProductName)
)
SELECT * FROM CTE UNION ALL SELECT * FROM CTE2 ORDER BY 1
J'ai accepté la réponse de Gordon car il était le premier, cela fonctionnait et je n'ai pas précisé quelle devrait être sa performance ou combien de lignes nous avons. Votre réponse s'est avérée beaucoup plus performante. J'ai annulé la solution de Gordon après environ une heure où la vôtre était de 7 secondes. Peut-être que c'est de notre faute bc d'index manquant mais je ne peux pas dire.
@ jrdev22 Heureux que cela fonctionne bien. J'ai exécuté un test supplémentaire et j'ai trouvé une erreur. Ma requête renvoie également abc en tant que parents pour abcd1 et abcd2, ce qui est faux. Vous ne voudrez peut-être pas faire quelques ajustements. Bonne chance!
oh, ce sera une tâche vraiment difficile à faire. L'une des rares fois où un curseur pourrait être la meilleure réponse