sql - 父子层次结构的根父级
问题描述
我指的是 Kimball 的数据仓库工具包的书,并希望实现父子层次结构。我提供以下代码来创建包含父子数据的表:
/****** Object: Table [dbo].[COMPANY] Script Date: 07/16/2020 15:05:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COMPANY](
[COMPANY_KEY] [int] NOT NULL,
[COMPANY_NAME] [varchar](50) NULL,
[PARENT_KEY] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (100, N'MICROSOFT', NULL)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (101, N'SOFTWARE', 100)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (102, N'CONSULTING', 101)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (103, N'PRODUCTS', 101)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (104, N'OFFICE', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (105, N'VISIO', 104)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (106, N'VISIO EUROPE', 105)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (107, N'BACK OFFICE', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (108, N'SQL SERVER', 107)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (109, N'OLAP SERVICES', 108)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (110, N'DTS', 108)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (111, N'REPOSITORY', 108)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (112, N'DEVELOPER TOOLS', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (113, N'WINDOWS', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (114, N'ENTERTAINMENT', 103)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (115, N'GAMES', 114)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (116, N'MULTIMEDIA', 114)
INSERT [dbo].[COMPANY] ([COMPANY_KEY], [COMPANY_NAME], [PARENT_KEY]) VALUES (117, N'EDUCATION', 101)
然后我使用以下查询:
WITH REC (Root_ID,Level,Company_key,Parent_key,Company_Name)
As
(
Select
t1.COMPANY_KEY,
1,
t1.COMPANY_KEY,
t1.PARENT_KEY,
t1.COMPANY_NAME
from company t1
where t1.PARENT_KEY ='103'
Union All
Select
REC.Root_ID,
REC.Level+1,
REC_PLUS1.COMPANY_KEY,
REC_PLUS1.PARENT_KEY,
REC_PLUS1.COMPANY_NAME
from COMPANY as REC_PLUS1, REC
where REC.Company_key=REC_PLUS1.PARENT_KEY
)
Select S1.COMPANY_Key, S1.Parent_key,Level as Depth_from_Parent
from REC S1
left outer join COMPANY t1
on S1.PARENT_KEY=t1.Company_key
我得到以下输出(表1):
除了上面显示的当前输出,我还需要以下输出(表 2),即通过包含根父级突出显示的部分,在这种情况下,我过滤了 Company_key=103:
在锚查询中,我输入了 Company_Key='103' 硬编码,但我想在这里使用这个来自另一个表的动态 ID,左连接。因此,对于该表中的每个 Company_Key,我应该得到按表 2 所示格式过滤的正确输出。
解决方案
如果我理解正确,您可以调整 CTE 以获取您想要的任何列表:
with cte as (
select company_key, company_name, company_key as parent_key, 0 as lev
from company
where company_key in (103, 107) -- whatever list you want here
union all
select c.company_key, c.company_name, cte.parent_key, 1 + cte.lev
from cte join
company c
on cte.company_key = c.parent_key
)
select *
from cte
order by parent_key, lev;
这是一个 db<>fiddle。
推荐阅读
- bash - find 和 xarg 组合需要计数器
- reactjs - React js过滤器不起作用,但搜索fileld即将到来
- go - Go - 无法解析嵌套结构
- ruby-on-rails - 如何处理 app.yaml 中的秘密而不是将其放入 GIT?
- django - 无法使用扩展用户模型来满足 Django 表单
- unity3d - 在 Unity 中将材质应用于从 Blender 导出的模型
- java - 如何使用 CP Optimizer 为提货和送货操作建模容量限制?
- facebook - 为什么以及如何在 aws cognito 上检索使用 facebook 登录的用户信息?
- python - Python Asyncio如何从队列中连续运行任务/协程
- apache-camel - Apache Camel - 类型转换期间的错误或误解行为