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

Откуда: Москва
Сообщений: 1139
День добрый,
возможно ли решить такую задачку для CTE?

Дано:
Таблица, где есть цепочки с данными
-- Создаем табличку
CREATE TABLE [dbo].[tbl_Test](
	[Entry No] [int] NOT NULL,
	[Transferred from entry No.] [int] NULL,
	[Row created date] [datetime] NULL,
 CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED 
(
	[Entry No] 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

-- Заполняем ее значениями - 2 цепочки. 
-- Предположим они были созданы вчера (дата создания - вчерашний день)
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (4266,0, DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (16380,4266, DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (16521,16380, DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (22488,0, DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (39677,22488, DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (39966,39677, DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (72714,39966,DATEADD(DAY, -1, GETDATE()))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (72813,72714, DATEADD(DAY, -1, GETDATE()))
GO

-- Выборка одной цепочки для примера. ID родительской записи 4266 
WITH MyCTE 
AS (
		SELECT	
			[Entry No], 
			[Transferred from entry No.],
			[Row created date]
		FROM  tbl_Test AS tbl_Main
		WHERE ([Entry No] = 4266)
					
		UNION ALL

		SELECT	tbl_Secondary.[Entry No], 
				tbl_Secondary.[Transferred from entry No.],
				tbl_Secondary.[Row created date]
			FROM tbl_Test AS tbl_Secondary
		INNER JOIN MyCTE as MyCTE2 ON tbl_Secondary.[Transferred from entry No.] = MyCTE2.[Entry No]
		WHERE (tbl_Secondary.[Transferred from entry No.] <> 0))

		SELECT * FROM MyCTE;

GO


Как видно, начало цепочки это строка, где [Transferred from entry No.] = 0. Все остальные строки в последовательности имеют собственный ID и ID родителя.

Проблема:
- начало цепочки может быть в одни день, а ее окончание через 2-3 дня.
Я могу определить все новые записи в таблице по дате создания. Но как по этим записям раскрутить сами цепочки? Т.е. если это не первая запись в последовательности, как найти ее родителя (где [Transferred from entry No.] = 0)?

+ Заполнение тестовыми данными для еще пяти цепочек
-- Начало пяти новых цепочек ([Transferred from entry No.] = 0)
-- Предположим их начали неделю назад (DATEADD(DAY, -7, GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (2998,0,DATEADD(DAY, -7, GETDATE())))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (2999,0,DATEADD(DAY, -7, GETDATE())))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3000,0,DATEADD(DAY, -7, GETDATE())))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3001,0,DATEADD(DAY, -7, GETDATE())))
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3002,0,DATEADD(DAY, -7, GETDATE())))


-- Окончания этих цепочек.
-- Предположим их внесли в таблицу сегодня 
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3073,2998,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (5944,3073,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (6013,5944,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3074,2999,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (5945,3074,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (6014,5945,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3075,3000,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (5946,3075,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (6015,5946,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3076,3001,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (5947,3076,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (6016,5947,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (3077,3002,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (5948,3077,GETDATE())
INSERT INTO tbl_Test ([Entry No], [Transferred from entry No.], [Row created date]) VALUES (6017,5948,GETDATE())
15 авг 13, 10:08    [14711398]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
рекурсию можно выполнять как от предков к потомкам, так и наоборот. Если цель стоит найти родителя у записи то поднимайся вверх пока tbl_Secondary.[Transferred from entry No.] = 0
15 авг 13, 10:24    [14711481]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
Мистер Хенки,
Т.е. брать каждую запись и искать родителя? А затем выбрать всех уникальных.

у меня как-то из головы вылетело, спасибо.
15 авг 13, 10:28    [14711504]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Gena928
Мистер Хенки,
Т.е. брать каждую запись и искать родителя? А затем выбрать всех уникальных.

у меня как-то из головы вылетело, спасибо.

я, честно говоря, плохо понял постановку задачи, но из того что я понял вы каким то образом ищете все новые записи, а потом вам надо найти их предка начального уровня. Тогда задача решается так: множество новых записей на входе у рекурсивного сте, для каждой из них ищем предков пока не дойдем до уровня 0 . соотвественно из множества полученного рекурсией выбираем только записи 0 уровня - это и будут начала цепочек для всех "новых" записей.
15 авг 13, 10:40    [14711555]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
Мистер Хенки,

да, в этом и есть задача.
Просто до меня не доперло что CTE может искать и в обратном порядке (т.е. от потомков к родителю).
15 авг 13, 10:48    [14711599]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
rthrthrthrt
Guest
Gena928,

"Но как по этим записям раскрутить сами цепочки?"

declare @e int = 39966;

--вверх
with q_up as
(
	select *
	from tbl_test
	where [Entry No] = @e

	union all

	select t2.*
	from
		q_up t1
			inner join
		tbl_test t2 on t1.[Transferred from entry No.] = t2.[Entry No]
),
q_down as
(
	select *
	from tbl_test
	where [Entry No] = @e

	union all

	select t2.*
	from
		q_down t1
			inner join
		tbl_test t2 on t1.[Entry No] = t2.[Transferred from entry No.]
)


select *
from q_up

union

select *
from q_down


не оно?
15 авг 13, 11:05    [14711675]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
rthrthrthrt,

оно, спасибо.
15 авг 13, 11:13    [14711707]     Ответить | Цитировать Сообщить модератору
 Re: Хитрая задачка для Common Table Expression  [new]
rthrthrthrt
Guest
Gena928
rthrthrthrt,

оно, спасибо.


+ выкинут distinct sort в конце

declare @e int = 39966;

--вверх
with q_up as
(
	select *
	from tbl_test
	where [Entry No] = @e

	union all

	select t2.*
	from
		q_up t1
			inner join
		tbl_test t2 on t1.[Transferred from entry No.] = t2.[Entry No]
),
q_down as
(
	select *
	from tbl_test
	where [Transferred from entry No.] = @e

	union all

	select t2.*
	from
		q_down t1
			inner join
		tbl_test t2 on t1.[Entry No] = t2.[Transferred from entry No.]
)


select *
from q_up

union all

select *
from q_down

15 авг 13, 11:28    [14711831]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить