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

Откуда:
Сообщений: 56
Имеется таблица размещенний.
вида id parentid name
id parentid name
1 0 Москва
2 1 ВАО
3 2 ул щелковская
4 3 дом 6
5 1 ЦАО
6 5 ул марьина роша
7 6 дом 9
8 0 самара
....
нужно написать запрос который выдаст результат в виде

id name1ur name2ur name3ur name4ur...
1 Москва
2 Москва ВАО
3 Москва ВАО ул Щелковская
4 Москва ВАО ул Щелковская дом 6
5 Москва ЦАО
......
7 сен 16, 12:46    [19636987]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Nikita Gavrilov,

в рекурсивном CTE накапливаете в поле конкатенацию name узлов дерева.
Или вам каждый уровень именно в отдельной колонке иметь надо?
7 сен 16, 12:49    [19637021]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
iap,
да в отдельном колонке.
7 сен 16, 12:56    [19637077]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Nikita Gavrilov
iap,
да в отдельном колонке.
Тогда надо PIVOT к рекурсивному CTE прикрутить.
7 сен 16, 13:12    [19637222]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
не получается с этой CTE разобраться

select placingid idp, ParentID pid, Brief br into #t from Placing where parentid is null
--create table #pl(idp int, pid int, nam varchar(50), p1 int, p2 int, p3 int, p4 int, p5 int, p6 int, p7 int, p8 int, p9 int, p10 int, lv int)
insert into #pl (idp, pid, nam, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, lv)
select idp,pid,br,idp,0,0,0,0,0,0,0,0,0,1 from #t
--select * from #pl
--order by item
declare @i1 int, @i2 int, @i3 int, @i4 int, @i5 int, @i6 int, @i7 int, @i8 int, @i9 int, @i10 int
set @i1=(select max(idp) from #t)
while @i1>=(select min(idp) from #t)  --подставить максимальный item из #t----
begin
select placingid idp, ParentID pid, Brief br into #t1 from Placing where parentid=@i1
insert into #pl (idp, pid, nam, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, lv)
select idp,pid,br,@i1,idp,0,0,0,0,0,0,0,0,2 from #t1
	set @i2=(select max(idp) from #t1)
	while @i2>=(select min(idp) from #t1)  --подставить максимальный item из #t----
	begin
	select placingid idp, ParentID pid, Brief br into #t2 from Placing where parentid=@i2
	insert into #pl (idp, pid, nam, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, lv)
	select idp,pid,br,@i1,@i2,idp,0,0,0,0,0,0,0,3 from #t2
		set @i3=(select max(idp) from #t2)
		while @i3>=(select min(idp) from #t2)  --подставить максимальный item из #t----
		begin
		select placingid idp, ParentID pid, Brief br into #t3 from Placing where parentid=@i3
		insert into #pl (idp, pid, nam, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, lv)
		select idp,pid,br,@i1,@i2,@i3,idp,0,0,0,0,0,0,4 from #t3
		drop table #t3
		delete from #t2 where idp=@i3
		set @i3=(select max(idp) from #t2)
		end
	drop table #t2
	delete from #t1 where idp=@i2
	set @i2=(select max(idp) from #t1)
	end
drop table #t1
delete from #t where idp=@i1
set @i1=(select max(idp) from #t)
end
drop table #t
--delete from #pl where lv>1
select * from #pl 


он рассчитан на 4 уровня вложенности, а у меня 10.
Как сделать это в 2 циклах
помогите

Сообщение было отредактировано: 9 сен 16, 19:50
9 сен 16, 12:17    [19646680]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Nikita Gavrilov
не получается с этой CTE разобраться
А где тут у вас CTE?
Что вы называете "CTE"?
Это, вообще-то, "Common Table Expression".

И оформляйте, пожалуйста, тегом SRС.
А то я сломал себе глаза.
9 сен 16, 12:30    [19646727]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
iap, Здесь его нет. это моя реализация. Мне нужна помощь в реализации этого в 2 циклах или 1 цикл + встроенная функция.
9 сен 16, 12:38    [19646755]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
create table t (id int primary key, pid int, br varchar(50),  p1 int, p2 int, p3 int, p4 int, p5 int, p6 int, p7 int, p8 int, p9 int, p10 int) 
insert t 
select PlacingID id, parentid pid, brief br, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 from Placing 
;with tree (id, pid, level, br,p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
as (select br, id, 0, cast('' as text) 
   from t
   where pid is null 
union all
   select t.br, t.id, t.level + 1, tree.pathstr + t.br,t.pid,t.p1, t.p2, t.p3, t.p4, t.p5, t.p6, t.p7, t.p8, t.p9, t.p10
   from t 
     inner join tree on tree.id = t.pid) 
select id, space( level ) + br as br, pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
from tree 
order by pathstr

дает ошибку "tree содержит меньше столбцов, чем указано в списке столбцов." Подскажите пожалуйста что не так.
9 сен 16, 13:32    [19647048]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
create table t (id int primary key, pid int, br varchar(50),  p1 int, p2 int, p3 int, p4 int, p5 int, p6 int, p7 int, p8 int, p9 int, p10 int, level int) 
insert t 
select PlacingID id, parentid pid, brief br, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,1 from Placing 
;with tree (br, id, level, pathstr, pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
as (select br, id, 0, cast('' as varchar(500)) ,pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
   from t
   where pid is null 
union all
   select t.br, t.id, t.level + 1, tree.pathstr + t.br,t.pid,t.p1, t.p2, t.p3, t.p4, t.p5, t.p6, t.p7, t.p8, t.p9, t.p10
   from t 
     inner join tree on tree.id = t.pid) 
select id, space( level ) + br as br, pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, br
from tree 
order by pathstr

новая ошибка "Несовпадение типов между связывающей и рекурсивной частями в столбце "pathstr" рекурсивного запроса "tree"."
пишу cast('' as Text) то несовпадение text и varchar
9 сен 16, 13:58    [19647239]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
 create table t (id int primary key, pid int, br varchar(500),  p1 int, p2 int, p3 int, p4 int, p5 int, p6 int, p7 int, p8 int, p9 int, p10 int, level int) 
insert t 
select PlacingID id, parentid pid, brief br, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,1 from Placing 
;with tree (br, id, level, pathstr, pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10)
as (select br, id, 0, cast('' as varchar(500)) ,pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
   from t
   where pid is null 
union all
   select t.br, t.id, t.level + 1, cast(tree.pathstr +' '+ t.br as varchar(500)), t.pid, t.p1, t.p2, t.p3, t.p4, t.p5, t.p6, t.p7, t.p8, t.p9, t.p10
   from t 
     inner join tree on tree.id = t.pid) 
select * --id, space( level ) + br as br, pid, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10
from tree 
order by pathstr

но результат не тот у меня минимум есть 4 уровня вложения а в выборке у тех объектов у которых левел должен быть 1 и 3 стоит левел 2
9 сен 16, 14:20    [19647340]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
Товарищи пожалуйста помогите добить этот запрос!
как прикрутить Pivot? В pathstr указан полный путь как его разбить на отдельные столбцы?
9 сен 16, 14:39    [19647411]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Nikita Gavrilov,

declare @t table (id int, parentid int, name varchar(100))
insert @t(id, parentid, name) values
(1, 0, 'Москва'),
(2, 1, 'ВАО'),
(3, 2, 'ул щелковская'),
(4, 3, 'дом 6'),
(5, 1, 'ЦАО'),
(6, 5, 'ул марьина роша '),
(7, 6, 'дом 9'),
(8, 0, 'самара')


;with cte as(
	select id, parentid, name name1, cast('' as varchar(100)) name2, cast('' as varchar(100)) name3, cast('' as varchar(100)) name4
	from @t
		union all
	select c.id, t.parentid, t.name, c.name1, c.name2, c.name3
	from cte c join @t t on c.parentid = t.id
)
select * from cte
where parentid = 0
order by id
9 сен 16, 15:53    [19647879]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Nikita Gavrilov
Member

Откуда:
Сообщений: 56
iljy,
Большое спасибо!!!!!
9 сен 16, 17:13    [19648321]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iljy
Member

Откуда:
Сообщений: 8711
Nikita Gavrilov,

да не за что, но это для вложенности не больше 4. Если надо для произвольной, то только динамика.
9 сен 16, 17:26    [19648378]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
uaggster
Member

Откуда:
Сообщений: 867
iljy, Динамика - да, но простенькая.

Вот без динамики:
Declare @t table (id int, parent_id int, name nvarchar(255))

insert into @t
Values
(1, 0, 'Москва'),
(2, 1, 'ВАО'),
(3, 2, 'ул щелковская'),
(4, 3, 'дом 6'),
(5, 1, 'ЦАО'),
(6, 5, 'ул марьина роша'),
(7, 6, 'дом 9'),
(8, 0, 'самара')

;With t as 
(Select id [root_id], [id], parent_id, Name,  0 [L]
From @t
Where parent_id=0
Union all
Select t3.id [root_id], t2.[id], t2.parent_id, t2.name,  t1.[L] + 1
From t t1 
inner join @t t2 on t1.id = t2.parent_id
inner join @t t3 on t3.id = t1.root_id
)
Select * from 
    (Select root_id, name, l
	   from t
    ) as p
    pivot
    (Min(name)
    For l in ([0],[1],[2],[3],[4],[5],[6],[7],[8], [9])
    ) as pvt


Для динамики нужно предварительно сделать select из СТЕ distinct L в виде строки, а потом воткнуть ее вместо [0],[1],[2],[3],[4],[5],[6],[7],[8],[9], не меняя всего остального.
9 сен 16, 17:42    [19648441]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
uaggster
Member

Откуда:
Сообщений: 867
Нет, соврал.
Не то выдает. Прощу прощения.
9 сен 16, 17:49    [19648467]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iljy
Member

Откуда:
Сообщений: 8711
uaggster,

ну да, простенькая
if object_id('tempdb..#t') is not null drop table #t

declare @t table (id int, parentid int, name varchar(100))
insert @t(id, parentid, name) values
(1, 0, 'Москва'),
(2, 1, 'ВАО'),
(3, 2, 'ул щелковская'),
(4, 3, 'дом 6'),
(5, 1, 'ЦАО'),
(6, 5, 'ул марьина роша '),
(7, 6, 'дом 9'),
(8, 0, 'самара')


;with cte as(
	select id, parentid, cast('<item>' + name + '</item>' as varchar(max)) name, 1 cnt
	from @t
		union all
	select c.id, t.parentid, '<item>' + t.name + '</item>' + c.name, cnt + 1
	from cte c join @t t on c.parentid = t.id
)
select id, CAST(name as xml) x, cnt
into #t 
from cte
where parentid = 0

declare @cnt int = (select MAX(cnt) from #t)

declare @sql varchar(max) = (
	select ', isnull(x.value(''item[' + cast(number as varchar) + ']'', ''varchar(max)''), '''') name' + cast(number as varchar)
	from master..spt_values
	where type = 'P' and number between 1 and @cnt
	for xml path('')
)

set @sql = 'select id' + @sql +' from #t order by id'

exec(@sql)
9 сен 16, 18:07    [19648543]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
uaggster
Member

Откуда:
Сообщений: 867
iljy
да не за что, но это для вложенности не больше 4. Если надо для произвольной, то только динамика.

iljy, объясните пожалуйста, почему это работает только до уровня вложенности 4.
Смотрю как баран на новые ворота, и не могу въехать!
9 сен 16, 19:28    [19648762]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iljy
Member

Откуда:
Сообщений: 8711
uaggster,

потому что поля в таблице 4. и если вложенность будет больше, то показываться будут только верхние 4, 5й уровень уйдет. Просто добавьте в таблицу строку типа (10, 4, 'подъезд 2'), и все увидите.
9 сен 16, 19:41    [19648813]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
uaggster
Member

Откуда:
Сообщений: 867
iljy, и еще, если возможно, объясните, почему Вы использовали вот такую конструкцию:
declare @cnt int = (select MAX(cnt) from #t)

declare @sql varchar(max) = (
	select ', isnull(x.value(''item[' + cast(number as varchar) + ']'', ''varchar(max)''), '''') name' + cast(number as varchar)
	from master..spt_values
	where type = 'P' and number between 1 and @cnt
	for xml path('')
)


А не вот такую:
declare @sql varchar(max) = (Select Distinct ', isnull(x.value(''item[' + cast(cnt as varchar) + ']'', ''varchar(max)''), '''') name' + cast(cnt as varchar)
    from #t
for xml path('')
)


В чем сакральный смысл то?

Я без иронии спрашиваю!
9 сен 16, 19:41    [19648816]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iljy
Member

Откуда:
Сообщений: 8711
uaggster,

да ни в чем, просто по привычке. Можно и по-вашему, только не DISTINCT, а GROUP BY cnt ORDER BY cnt, потому что DISTINCT будет, во-первых, группировать текст, что медленнее, а во-вторых - не гарантирует порядок, что критично для выдачи.
9 сен 16, 19:45    [19648834]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
uaggster
Member

Откуда:
Сообщений: 867
iljy
uaggster,

да ни в чем, просто по привычке. Можно и по-вашему, только не DISTINCT, а GROUP BY cnt ORDER BY cnt, потому что DISTINCT будет, во-первых, группировать текст, что медленнее, а во-вторых - не гарантирует порядок, что критично для выдачи.

Да, order by нужен, это я протупил.
А почему DISTINCT хуже GROUP BY - не понял, честно говоря. Планы ж должны быть одинаковы!
Вроде бы...?

Про 4 уровень понял, что я первоначально не понял, что Вы хотели сказать. Я пытался понять, почему такая конструкция:
declare @t table (id int, parentid int, name varchar(100))
insert @t(id, parentid, name) values
(1, 0, 'Москва'),
(2, 1, 'ВАО'),
(3, 2, 'ул щелковская'),
(4, 3, 'дом 6'),
(5, 1, 'ЦАО'),
(6, 5, 'ул марьина роша '),
(7, 6, 'дом 9'),
(9, 7, 'кв 9'),
(8, 0, 'самара')

;with cte as(
	select id, parentid, name name1, cast('' as varchar(100)) name2, cast('' as varchar(100)) name3, cast('' as varchar(100)) name4, cast('' as varchar(100)) name5
	from @t
		union all
	select c.id, t.parentid, t.name, c.name1, c.name2, c.name3,  c.name4
	from cte c join @t t on c.parentid = t.id
)
select * from cte
where parentid = 0
order by id

не будет работать.
При том, что она то вполне себе работает :-)

Кстати, большое спасибо за примеры.
Правда понять как это работает сложно. Пока просто привыкаю.
9 сен 16, 19:53    [19648865]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iljy
Member

Откуда:
Сообщений: 8711
uaggster
А почему DISTINCT хуже GROUP BY - не понял, честно говоря. Планы ж должны быть одинаковы!
Вроде бы...?


Планы - одинаковые, а группируемые поля - разные. GROUP BY cnt будет работать с четырехбайтным целым числом, а DISTINCT - с вычисляемой текстовой строкой переменной длины, что явно менее эффективно, такие вещи быстро привыкаешь делать на автомате. Ну и явную группировку гораздо проще контролировать при сопровождении кода.
9 сен 16, 20:20    [19648962]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
uaggster
Member

Откуда:
Сообщений: 867
iljy
uaggster
А почему DISTINCT хуже GROUP BY - не понял, честно говоря. Планы ж должны быть одинаковы!
Вроде бы...?


Планы - одинаковые, а группируемые поля - разные. GROUP BY cnt будет работать с четырехбайтным целым числом, а DISTINCT - с вычисляемой текстовой строкой переменной длины, что явно менее эффективно, такие вещи быстро привыкаешь делать на автомате. Ну и явную группировку гораздо проще контролировать при сопровождении кода.

Всё понял, спасибо.
Раньше не задумывался над этим.
9 сен 16, 21:27    [19649247]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить