让孩子和所有父母使用SQL? [英] get child and all parents using sql ?
本文介绍了让孩子和所有父母使用SQL?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨所有
我创建表名称家族
包括memberID,Name,ParentID
和它包含数据
所以如何使用其ID选择特定名称并获取此孩子的全名
姓名+父母姓名2 +父母姓名1父母姓名2 +父母姓名1
请帮助:)
解决方案
假设您使用的是SQL Server,则可以使用CTE发出递归查询: https://technet.microsoft.com/en-us/library/ms186243(v = sql.105).aspx [ ^ ]
在Oracle中,您有SELECT LEVEL
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.ht m [ ^ ]
在MySQL中,您还可以发出分层查询:
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ [ ^ ]
SQL Server示例:
CREATE TABLE [dbo]。[family](
[id] [ int ] NULL ,
[name] [ nvarchar ]( 50 ) NULL ,
[parent] [ int ] NULL
) ON [ PRIMARY ]
GO
INSERT [dbo]。[family]([id],[name],[parent]) VALUES ( 1 ,N ' Mike', NULL )
GO
INSERT [dbo]。[family]([id],[name],[parent]) VALUES ( 2 ,N ' John', 1 )
GO
INSERT [dbo]。[ family]([id],[name],[parent]) VALUES ( 3 ,N ' Wilth', 1 )
GO
INSERT [dbo] 。[family]([id],[name],[parent]) VALUES ( 4 ,N ' Sam', 2 )
GO
INSERT [dbo]。[family]([id],[name] ],[parent]) VALUES ( 5 ,N ' Malik', 2 )
GO
INSERT [dbo]。[family]([id],[name],[parent]) VALUES ( 6 ,N ' Joly', 5 )
GO
WITH Ancessors(ParentID,ID,Name,Level)
AS
(
- 主要成员定义
< span class =code-keyword> SELECT e.parent,e.id,e.Name,
0 AS 级别
FROM dbo.family AS e
WHERE e.id = 6
UNION ALL
- - 递归成员定义
SELECT e.parent,e.id,e .Name,
等级+ 1
FROM dbo.family AS e
INNER JOIN Ancessors AS d
ON e.id = d.ParentID
)
- 执行CTE的语句
SELECT ParentID,ID,Name,Level
FROM Ancessors
GO
hi all
I create table Name Family
include memberID , Name , ParentID
and it contain data
so how can I select specific Name using its id and get full name for this child
name + parent name 2 + parent name 1 for parent name 2 + parent name 1
please help :)
解决方案
Supposing you are using SQL Server, you can use CTE to issue recursive query: https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx[^]
In Oracle you have SELECT LEVEL
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm[^]
In MySQL you can also issue hierarchical queries:
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/[^]
SQL Server example:
CREATE TABLE [dbo].[family]( [id] [int] NULL, [name] [nvarchar](50) NULL, [parent] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[family] ([id], [name], [parent]) VALUES (1, N'Mike', NULL) GO INSERT [dbo].[family] ([id], [name], [parent]) VALUES (2, N'John', 1) GO INSERT [dbo].[family] ([id], [name], [parent]) VALUES (3, N'Wilth', 1) GO INSERT [dbo].[family] ([id], [name], [parent]) VALUES (4, N'Sam', 2) GO INSERT [dbo].[family] ([id], [name], [parent]) VALUES (5, N'Malik', 2) GO INSERT [dbo].[family] ([id], [name], [parent]) VALUES (6, N'Joly', 5) GO WITH Ancessors (ParentID, ID, Name, Level) AS ( -- Anchor member definition SELECT e.parent, e.id, e.Name, 0 AS Level FROM dbo.family AS e WHERE e.id = 6 UNION ALL -- Recursive member definition SELECT e.parent, e.id, e.Name, Level + 1 FROM dbo.family AS e INNER JOIN Ancessors AS d ON e.id = d.ParentID ) -- Statement that executes the CTE SELECT ParentID, ID, Name, Level FROM Ancessors GO
这篇关于让孩子和所有父母使用SQL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文