文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何在同一表中映射父子关系中的自动映射

问如何在同一表中映射父子关系中的自动映射
EN

Stack Overflow用户
提问于 2019-11-20 08:03:52
回答 3查看 161关注 0票数 1

我在数据库中有一个带有数据的SQL表。

但是现在需求已经改变了，我在表中引入了一个新列ParentID

表结构

代码语言：javascript
复制
CREATE TABLE [dbo].[EopsModule](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ModuleCode] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](200) NOT NULL,
    [IsDefaultModule] [bit] NOT NULL
 CONSTRAINT [PK_EopsModule] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

这里是表的输出

代码语言：javascript
复制
SELECT * FROM EopsModule

//Output

ID  ModuleCode  Description IsDefaultModule
1   EW001.00    Webshop standaard   1
2   EW001.01    Algemene functionaliteit en configuratie    1
3   EW001.01.1  Single- of Multishop    1
4   EW001.01.1.1    Optioneel: Multishop    1
5   EW001.01.2  Taal    1
6   EW001.01.2.1    Optioneel: Multi taal   1
7   EW001.02    Design & Lay-out    1
8   EW001.02.1  Standaard Lay-out wireframe / elementen 1
9   EW001.02.2  Responsive / Mobile Friendly    1
10  EW001.03    Content 1
11  EW001.03.1  Optioneel: Pagina artikelen (Website module)    1
12  EW001.03.1.1    Optioneel: Content header & footer (Website module) 1
13  EW001.03.2  Pagina Teksten  1
14  EW001.03.3  Optioneel: Slider (Website module)  1
15  EW001.03.4  Contact pagina  1
16  EW001.03.5  Optioneel: Bel me terug 1
17  EW001.03.6  Optioneel: FAQ (Website module) 1
18  EW001.03.7  Optioneel: Nieuws (Website module)  1
19  EW001.03.8  Navigatie menu  1
20  EW001.03.8.1    Assortiment navigatie uitklapmenu   1
21  EW001.03.9  Pagina Tracking Scripts 1
22  EW001.03.10 URL Re-directs  1
23  EW001.03.11 Cookie Notificatie / Privacy Policy 1
24  EW001.03.12 Error pagina’s  1
25  EW001.04    Login & Registratie 1
26  EW001.04.1  Inloggen    1
27  EW001.04.2  Inloggen met e-mailadres    1
28  EW001.04.3  Wachtwoord vergeten 1
29  EW001.04.4  Registreren 1
30  EW001.04.4.1    Goedkeuringsprincipe    1
31  EW001.04.4.2    Geen registratie    1
32  EW001.04.4.3    Open registratie    1
33  EW001.04.4.4    Optioneel: Gast-account 1
34  EW001.04.4.5    Registratieformulier    1
35  EW001.04.4.6    Registratie met ERP Data koppeling  1
36  EW001.05    Assortiment 1
37  EW001.05.1  Structuur   1
38  EW001.05.2  Product detail  1
39  EW001.05.3  Optioneel: Product relaties 1
40  EW001.05.4  Assortiment content (extra informatie)  1
41  EW001.05.5  Optioneel: Top Items    1
42  EW001.05.6  Optioneel: Recent bekeken producten 1
43  EW001.06    Zoeken  1
44  EW001.06.1  Standaard zoeken    1
45  EW001.06.2  Normaliseren    1
46  EW001.07    Bestellen   1
47  EW001.07.1  Winkelwagen 1
48  EW001.07.1.1    Optioneel: Snel bestellen   1
49  EW001.07.1.2    Optioneel: Orderregel opmerking 1
50  EW001.07.1.3    Optioneel: Winkelwagen beheer   1
51  EW001.07.2  Gegevens    1
52  EW001.07.3  Betaling    1
53  EW001.07.3.1    Op rekening 1
54  EW001.07.4  Controleren 1
55  EW001.07.4.1    Optioneel: Orderreferentie  1

在表中添加ParentID列之后，我需要基于module code映射主id

代码语言：javascript
复制
ALTER TABLE [dbo].[EopsModule] ADD [ParentID] INT NULL

SELECT *
FROM [dbo].[EopsModule] WITH (NOLOCK)
INNER JOIN
(
SELECT
[BASE].[ModuleCode]
,   [BASE].[ParentCode]
,   [Parent].[ID] AS [ParentID]
FROM
(
SELECT
[EopsModule].[ModuleCode]
,   REPLACE( [EopsModule].[ModuleCode], RIGHT( [EopsModule].[ModuleCode], CHARINDEX( '.', REVERSE( [EopsModule].[ModuleCode] ), 0 )), '' ) AS [ParentCode]
FROM [dbo].[EopsModule] WITH (NOLOCK)
GROUP BY [EopsModule].[ModuleCode]
) AS [BASE]
LEFT JOIN [dbo].[EopsModule] AS [Parent] WITH (NOLOCK)
ON [BASE].[ParentCode] = [Parent].[ModuleCode]
) AS [Parent]
ON [EopsModule].[ModuleCode] = [Parent].[ModuleCode]

上面的

查询输出

代码语言：javascript
复制
ID  ModuleCode  Description IsDefaultModule ParentID    ModuleCode  ParentCode  ParentID
167 EB001.00    Budgetshop  0   NULL    EB001.00    EB001   NULL
168 EB002.00    Webshop Management  0   NULL    EB002.00    EB002   NULL
169 EB003.00    Orders goedkeuren   0   NULL    EB003.00    EB003   NULL
170 EB004.00    Single sign-on  0   NULL    EB004.00    EB004   NULL
171 ED100.00    Dealer portaal  0   NULL    ED100.00    ED100   NULL
172 EM001.00    Maatwerk    0   NULL    EM001.00    EM001   NULL
1   EW001.00    Webshop standaard   1   NULL    EW001.00    EW001   NULL
2   EW001.01    Algemene functionaliteit en configuratie    1   NULL    EW001.01    EW001   NULL
3   EW001.01.1  Single- of Multishop    1   2   EW001.01.1  EW001.01    2
4   EW001.01.1.1    Optioneel: Multishop    1   2   EW001.01.1.1    EW001.01    2
5   EW001.01.2  Taal    1   2   EW001.01.2  EW001.01    2
6   EW001.01.2.1    Optioneel: Multi taal   1   5   EW001.01.2.1    EW001.01.2  5
7   EW001.02    Design & Lay-out    1   NULL    EW001.02    EW001   NULL
8   EW001.02.1  Standaard Lay-out wireframe / elementen 1   7   EW001.02.1  EW001.02    7
9   EW001.02.2  Responsive / Mobile Friendly    1   7   EW001.02.2  EW001.02    7
10  EW001.03    Content 1   NULL    EW001.03    EW001   NULL
11  EW001.03.1  Optioneel: Pagina artikelen (Website module)    1   10  EW001.03.1  EW001.03    10
12  EW001.03.1.1    Optioneel: Content header & footer (Website module) 1   10  EW001.03.1.1    EW001.03    10
22  EW001.03.10 URL Re-directs  1   10  EW001.03.10 EW001.03    10
23  EW001.03.11 Cookie Notificatie / Privacy Policy 1   10  EW001.03.11 EW001.03    10
24  EW001.03.12 Error pagina’s  1   10  EW001.03.12 EW001.03    10
13  EW001.03.2  Pagina Teksten  1   10  EW001.03.2  EW001.03    10
14  EW001.03.3  Optioneel: Slider (Website module)  1   10  EW001.03.3  EW001.03    10
15  EW001.03.4  Contact pagina  1   10  EW001.03.4  EW001.03    10
16  EW001.03.5  Optioneel: Bel me terug 1   10  EW001.03.5  EW001.03    10
17  EW001.03.6  Optioneel: FAQ (Website module) 1   10  EW001.03.6  EW001.03    10
18  EW001.03.7  Optioneel: Nieuws (Website module)  1   10  EW001.03.7  EW001.03    10
19  EW001.03.8  Navigatie menu  1   10  EW001.03.8  EW001.03    10
20  EW001.03.8.1    Assortiment navigatie uitklapmenu   1   19  EW001.03.8.1    EW001.03.8  19
21  EW001.03.9  Pagina Tracking Scripts 1   10  EW001.03.9  EW001.03    10
25  EW001.04    Login & Registratie 1   NULL    EW001.04    EW001   NULL
26  EW001.04.1  Inloggen    1   25  EW001.04.1  EW001.04    25
27  EW001.04.2  Inloggen met e-mailadres    1   25  EW001.04.2  EW001.04    25
28  EW001.04.3  Wachtwoord vergeten 1   25  EW001.04.3  EW001.04    25
29  EW001.04.4  Registreren 1   25  EW001.04.4  EW001.04    25
30  EW001.04.4.1    Goedkeuringsprincipe    1   29  EW001.04.4.1    EW001.04.4  29
31  EW001.04.4.2    Geen registratie    1   29  EW001.04.4.2    EW001.04.4  29
32  EW001.04.4.3    Open registratie    1   29  EW001.04.4.3    EW001.04.4  29
33  EW001.04.4.4    Optioneel: Gast-account 1   25  EW001.04.4.4    EW001.04    25
34  EW001.04.4.5    Registratieformulier    1   29  EW001.04.4.5    EW001.04.4  29
35  EW001.04.4.6    Registratie met ERP Data koppeling  1   29  EW001.04.4.6    EW001.04.4  29
36  EW001.05    Assortiment 1   NULL    EW001.05    EW001   NULL
37  EW001.05.1  Structuur   1   36  EW001.05.1  EW001.05    36
38  EW001.05.2  Product detail  1   36  EW001.05.2  EW001.05    36
39  EW001.05.3  Optioneel: Product relaties 1   36  EW001.05.3  EW001.05    36
40  EW001.05.4  Assortiment content (extra informatie)  1   36  EW001.05.4  EW001.05    36
41  EW001.05.5  Optioneel: Top Items    1   36  EW001.05.5  EW001.05    36
42  EW001.05.6  Optioneel: Recent bekeken producten 1   36  EW001.05.6  EW001.05    36
43  EW001.06    Zoeken  1   NULL    EW001.06    EW001   NULL
44  EW001.06.1  Standaard zoeken    1   43  EW001.06.1  EW001.06    43
45  EW001.06.2  Normaliseren    1   43  EW001.06.2  EW001.06    43
173 EW001.06.3  Optioneel: Zoeken op klant artikelnummer    1   43  EW001.06.3  EW001.06    43
46  EW001.07    Bestellen   1   NULL    EW001.07    EW001   NULL
47  EW001.07.1  Winkelwagen 1   46  EW001.07.1  EW001.07    46
48  EW001.07.1.1    Optioneel: Snel bestellen   1   46  EW001.07.1.1    EW001.07    46
49  EW001.07.1.2    Optioneel: Orderregel opmerking 1   47  EW001.07.1.2    EW001.07.1  47
50  EW001.07.1.3    Optioneel: Winkelwagen beheer   1   47  EW001.07.1.3    EW001.07.1  47
51  EW001.07.2  Gegevens    1   46  EW001.07.2  EW001.07    46
52  EW001.07.3  Betaling    1   46  EW001.07.3  EW001.07    46
53  EW001.07.3.1    Op rekening 1   52  EW001.07.3.1    EW001.07.3  52
54  EW001.07.4  Controleren 1   46  EW001.07.4  EW001.07    46
55  EW001.07.4.1    Optioneel: Orderreferentie  1   54  EW001.07.4.1    EW001.07.4  54

注意:数据已通过使用上述查询进行映射，但我注意到输出中存在异常，即每个子模块都有映射的父id，但对于第三级或第四级子模块，父模块代码和子模块代码之间存在冲突，最后，父模块代码和子模块代码的id不正确，请查找屏幕截图，以便更好地理解。

​

sql-server
sql
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2019-11-21 11:49:45

这是为我工作，请更改您的查询，而不是

代码语言：javascript
复制
 REPLACE( [EopsModule].[ModuleCode], RIGHT( [EopsModule].[ModuleCode], CHARINDEX( '.', REVERSE( [EopsModule].[ModuleCode] ), 0 )), '' ) AS [ParentCode]

尝试这一点，因为子字符串将返回‘'.’之后的确切值。

代码语言：javascript
复制
SUBSTRING( [EopsModule].[ModuleCode], 0, ( LEN( [EopsModule].[ModuleCode] ) 
- ( CHARINDEX( ''.'', REVERSE( [EopsModule].[ModuleCode] )) - 1 ))) 
AS [ParentCode]

输出：

​

​

票数 1
EN

Stack Overflow用户

发布于 2019-11-20 09:22:44

根据我的理解，

代码语言：javascript
复制
select p.id ParentId, p.ModuleCode ParentCode, c.ID, c.ModuleCode, c.Description 
from ##eopsmodule c 
left join ##eopsmodule p 
on c.modulecode like p.modulecode + '.' + '[1-9]' -- RE for only one digit. Not accept like '11', '24', '89'

产出如下：

代码语言：javascript
复制
ParentId    ParentCode      ID  ModuleCode      Description 
NULL        NULL            1   EW001.00        Webshop standaard   
NULL        NULL            2   EW001.01        Algemene functionaliteit en configuratie
2           EW001.01        3   EW001.01.1      Single- of Multishop    
3           EW001.01.1      4   EW001.01.1.1    Optioneel: Multishop    
2           EW001.01        5   EW001.01.2      Taal    
5           EW001.01.2      6   EW001.01.2.1    Optioneel: Multi taal   
NULL        NULL            7   EW001.02        Design & Lay-out    
7           EW001.02        8   EW001.02.1      Standaard Lay-out wireframe / elementen 
7           EW001.02        9   EW001.02.2      Responsive / Mobile Friendly    
NULL        NULL            10  EW001.03        Content                             
10          EW001.03        11  EW001.03.1      Optioneel: Pagina artikelen (Website module)    
11          EW001.03.1      12  EW001.03.1.1    Optioneel: Content header & footer (Website module) 
10          EW001.03        13  EW001.03.2      Pagina Teksten
票数 2
EN

Stack Overflow用户

发布于 2019-11-20 08:59:00

不是真正的解决方案，而是解释发生的事情的提示。

您的右/反向子字符串有时会返回类似于'.2‘的值(例如，对于.1 .3 .4 .等等)

然后，使用该值的替换命令有时会出现双匹配，例如使用for 001.02.2.2

然后保留一个坏的parentCode，例如of 001.02而不是of 001.02.2。

您可能会找到另一种方法来检索每个元素的好父级。

编辑

因为这只是您需要匹配的层次结构的最后一层，所以可以使用后缀和替换来获得正确的父代码：

代码语言：javascript
复制
REPLACE( [EopsModule].[ModuleCode] + '@', RIGHT( [EopsModule].[ModuleCode], CHARINDEX( '.', REVERSE( [EopsModule].[ModuleCode] ), 0 )) + '@', '' ) AS [ParentCode]

那么替换将只发生一次。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/58949405

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档