SQL - find parent in same column

问题: I cant express the problem correctly to find the answer online so i'm hoping someone could provide me with a link to a solution because i think this is a rather common task...

问题:

I cant express the problem correctly to find the answer online so i'm hoping someone could provide me with a link to a solution because i think this is a rather common task.

We have a hierachy of products and want to determine the parents. All product names are in the same column and the logic is the following

ProductId ProductName
-----------------------------------------------------------------------------
1         ABC
2         ABCD
3         ABCD1
4         ABCD2

Result should be

ABCD1 & ABCD2 are children of ABCD and ABCD is child of ABC

ProductId ProductName ParentName ParentId
------------------------------------------------------------------------------
1         ABC         NULL       NULL
2         ABCD        ABC        1
3         ABCD1       ABCD       2
...

回答1:

Hmmm. I think this does what you want:

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;

回答2:

No doubt that gordon's answer is best here, but still I gave it a go as well:

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

回答3:

If there's only 1 character difference.
Then you can LEFT JOIN to the ProductName & one wildcard character '_'

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;

Example snippet:

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;

Result:

ProductId   ProductName ParentName  ParentId
1           ABC         NULL        NULL
2           ABCD        ABC         1
3           ABCD1       ABCD        2
4           ABCD2       ABCD        2

If it's possible that there's more than 1 character difference then:

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);

回答4:

Did you try using Case with the condition and represent each condition as a new column. You can refer to the syntax https://www.w3schools.com/sql/sql_case.asp


回答5:

You can use Common Table Expression (CTE) to do the job.

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

The first part product_table must be replaced by your own product table. It is used here to generate a tempory dataset.

Your final query will look like:

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

CTE is available since SQL2008. for more info WITH common_table_expression (Transact-SQL)

  • 发表于 2019-01-11 18:06
  • 阅读 ( 237 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除