Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
Стоит задача - есть таблица где: Child_SID - ИД ребенка, ИД Родителя, тип Родителя (1(мама)/2(папа))
CREATE TABLE [dbo].[Pedigree](
[Child_SID] [int] NOT NULL,
[Parent_SID] [int] NOT NULL,
[TypeofParent] [int] NULL,
CONSTRAINT [PK_Pedigree] PRIMARY KEY CLUSTERED
(
[Child_SID] ASC,
[Parent_SID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(270,1012,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(270,55555,2)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(401,1012,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(401,55555,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(1012,3651,2)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(3651,5505,2)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(1012,7289,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(7289,2016,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(9454,270,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(9454,88888,2)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(9455,270,1)
INSERT INTO [Pedigree]([Child_SID],[Parent_SID],[TypeofParent]) VALUES(9455,88888,2)
GO

надо получить выборку которая на вход получает ИД, по этому ИД в обе стороны (в одну дети этого ИД, в другую предки этого ИД) получить списки детей и родителей в каждой строчке должен быть уровень. Например:

Level | ID | Mother | Father
-2 | 5505 | NULL | NULL
-2 | 2016 | NULL | NULL
-1 | 3651 | NULL | 5505
-1 | 7289 | 2016 | NULL
0 | 1012 | 7289 | 3651
1 |270 | 1012 | 55555
1 |401 | 1012 | 55555
2 |9454 | 270 | 88888
2 |9455 | 270 | 88888


Помогите идеями. Может таблицу первоначальную надо строить по другому?
15 дек 09, 10:59    [8067528]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Heart
Может таблицу первоначальную надо строить по другому?
Вариант
хранить в исходных данных два поля [Mother] и [Father]
вместо двух полей [Parent_SID],и [TypeofParent]
не рассматривали?
15 дек 09, 11:12    [8067631]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
aleks2
Guest
Ну... на СЕМЕРЫХ родителей закладываетесь?

Чем
CREATE TABLE [dbo].[Pedigree](
[Child_SID] [int] NOT NULL,
[Mother_SID] [int] NOT NULL,
[Father_SID] [int] NOT NULL
)
не устраивает?
15 дек 09, 11:12    [8067634]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
Нет проблем переделать таблицу.
А потом что делать?
Как, подав код персоны, вытащить всю генеалогию используя только ТSQL??
Родителей и детей неограничено количество
15 дек 09, 12:16    [8068178]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
vino
Member

Откуда:
Сообщений: 1191
Heart
Как, подав код персоны, вытащить всю генеалогию используя только ТSQL??
Родителей и детей неограничено количество

как и обычно, только записей будет раза в два меньше , или, действительно, у ребенка можете хранить больше одной мамы или папы?
15 дек 09, 12:34    [8068316]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
aleks2
Guest
Чо проще?

CREATE TABLE [dbo].[Pedigree](
[ID] [int] NOT NULL,
[MID] [int] NOT NULL,
[FSID] [int] NOT NULL
)

;with Tree(level, id, mid, fid)
as
( 
select 0 level, id, mid, fid FROM Pedigree WHERE ID=@ID
UNION ALL
select t.level+1 level, p.id, p.mid, p.fid 
FROM Tree T inner join Pedigree p ON T.fid=p.id
UNION ALL
select t.level+1 level, p.id, p.mid, p.fid 
FROM Tree T inner join Pedigree p ON T.mid=p.id
)
select * 
FROM Tree
ORDER BY level
15 дек 09, 12:37    [8068346]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
Спасибо. Получили родителей. А как детей получить там же?
15 дек 09, 12:57    [8068512]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
aleks2
Guest
Heart
Спасибо. Получили родителей. А как детей получить там же?


Пжалуста.
А подумать головой?
15 дек 09, 13:00    [8068540]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
:(
ну помогите. Плиз...
15 дек 09, 13:11    [8068617]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
Получилось 2 запроса. Один получает детей, другой родителей. А как их объеденить?
автор
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pedigree]') AND type in (N'U'))
DROP TABLE [dbo].[Pedigree]
GO


CREATE TABLE [dbo].[Pedigree](
[ID] [int] NOT NULL,
[MID] [int] NULL,
[FID] [int] NULL
)

INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(270,1012,55555)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(401,1012,55555)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(1012,3651,7289)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(3651,NULL,5505)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(7289,2016,NULL)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(9454,270,88888)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(9455,270,88888)

DECLARE @ID INT
SET @ID = 1012

;with Tree(level, id, mid, fid)
as
(
select 0 level, id, mid, fid FROM Pedigree WHERE ID=@ID
UNION ALL
select t.level-1 level, p.id, p.mid, p.fid
FROM Tree T inner join Pedigree p ON T.fid=p.id
UNION ALL
select t.level-1 level, p.id, p.mid, p.fid
FROM Tree T inner join Pedigree p ON T.mid=p.id
)
select *
FROM Tree
ORDER BY level

with Tree1(level, id, mid, fid)
as
(
select 1 level, id, mid, fid FROM Pedigree WHERE mid=@ID OR fid=@ID
UNION ALL
select t.level+1 level, p.id, p.mid, p.fid
FROM Tree1 T inner join Pedigree p ON T.id=p.fid
UNION ALL
select t.level+1 level, p.id, p.mid, p.fid
FROM Tree1 T inner join Pedigree p ON T.id=p.mid
)

select *
FROM Tree1
15 дек 09, 14:50    [8069461]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
автор
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pedigree]') AND type in (N'U'))
DROP TABLE [dbo].[Pedigree]
GO


CREATE TABLE [dbo].[Pedigree](
[ID] [int] NOT NULL,
[MID] [int] NULL,
[FID] [int] NULL
)

INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(270,1012,55555)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(401,1012,55555)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(1012,3651,7289)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(3651,NULL,5505)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(7289,2016,NULL)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(9454,270,88888)
INSERT INTO [Pedigree]([ID],[MID],[FID]) VALUES(9455,270,88888)

DECLARE @ID INT
SET @ID = 1012

;with Tree(level, id, mid, fid)
as
(
select 0 level, id, mid, fid FROM Pedigree WHERE ID=@ID
UNION ALL
select t.level-1 level, p.id, p.mid, p.fid
FROM Tree T inner join Pedigree p ON T.fid=p.id
UNION ALL
select t.level-1 level, p.id, p.mid, p.fid
FROM Tree T inner join Pedigree p ON T.mid=p.id
)
select *
FROM Tree
ORDER BY level

;with Tree1(level, id, mid, fid)
as
(
select 1 level, id, mid, fid FROM Pedigree WHERE mid=@ID OR fid=@ID
UNION ALL
select t.level+1 level, p.id, p.mid, p.fid
FROM Tree1 T inner join Pedigree p ON T.id=p.fid
UNION ALL
select t.level+1 level, p.id, p.mid, p.fid
FROM Tree1 T inner join Pedigree p ON T.id=p.mid
)

select *
FROM Tree1
ORDER BY level
15 дек 09, 14:51    [8069480]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Heart
А как их объеденить?
Результирующие наборы обычно объединяются в единый результирующий набор через UNION (ALL)
например
;with cte1 as (
   select 1 as id union all
   select 2 as id
), cte2 as (
   select 10 as id union all
   select 20 as id
)
select id 
  from cte1 
union all
select id 
  from cte2
15 дек 09, 15:29    [8069760]     Ответить | Цитировать Сообщить модератору
 Re: как получить выборку генеалогии - дети родители?  [new]
Heart
Member

Откуда:
Сообщений: 227
просто огромнейшее спасибо всем. спасли. век буду жить век буду учиться. первый раз использую такие вещи. СПАСИБО!!!!!!!!
15 дек 09, 15:42    [8069869]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить