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

Откуда: Калуга
Сообщений: 145
Есть две таблички.
table1:
id name
1 расцветали
2 яблони
3 и
4 груши

table2:
id ids
1 1,2,3,4
2 1,4,3,2
3 1,3,4,3,2

ids - это id из table1 через запятую.
нужно написать такой запрос, который бы вывел что-то вроде:
1 расцветали яблони и груши
2 расцветали груши и яблони
3 расцветали и груши и яблони

как-то так:) кто-нибудь сталкивался с такой организацией данных? помогите написать запросик:)
7 авг 13, 19:00    [14677772]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
Empirical
Member

Откуда:
Сообщений: 99
1. ids в несколько колонок раскинуть
2. добавить столько же с текстом, подставить туда значения update table set text1 = t2.text from table t1 inner join table t2 t1.id = t2.ids1
3. concatenate isnull(text1)+isnull(text2)+...
7 авг 13, 22:18    [14678318]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
aleksey_sql
Member

Откуда: Калуга
Сообщений: 145
Empirical
1. ids в несколько колонок раскинуть
2. добавить столько же с текстом, подставить туда значения update table set text1 = t2.text from table t1 inner join table t2 t1.id = t2.ids1
3. concatenate isnull(text1)+isnull(text2)+...


а насчет 1 и 2 пункта можно подробнее? как раскидать ids в несколько колонок?
7 авг 13, 22:25    [14678330]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
aleksey_sql
как раскидать ids в несколько колонок?


Их надо не в несколько колонок раскидывать, а в строки, например, с помощью табличной функции, с которой потом делать JOIN.
7 авг 13, 23:00    [14678421]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
wqefgqowiuefgiwe
Guest
aleksey_sql,

вот такой гамнакодец у меня получился (принципиально не хотелось пилить вспомогательные функции). работает при условии что id в table1 одноциферные. для многоциферных ( > 9) прийдется пилить функцию, скорее всего.

;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
rt(n) as (select row_number() over(order by (select 0)) from l4 t1, l4 t2),
t1(id,ids) as
(
	select 1, '1,2,3,4' union all
	select 2, '1,4,3,2' union all
	select 3, '1,3,4,3,2'
),
t2(id, name) as
(
	select 1,'расцветали' union all
	select 2,'яблони' union all
	select 3,'и' union all
	select 4,'груши'
),
t3 as
(
	select *, substring(ids, (n - 1) * 2 + 1, 1) as id_t2
	from
		t1
			cross apply
		(
			select top(LEN(REPLACE(
							  REPLACE(
								REPLACE(
								  REPLACE(
									REPLACE(
									  REPLACE(
										REPLACE(
										  REPLACE(
											REPLACE(
											  REPLACE(
												ids,
											  '9', ''),
											'8', ''),
										  '7', ''),
										'6', ''),
									  '5', ''),
									'4', ''),
								  '3', ''),
								'2', ''),
							  '1', ''),
							'0', '')) + 1
					  ) n
			from rt
		) as q
)
select
	t1.id,
	(
		select name + ' '
		from
			t2
				inner join
			t3 on t2.id = t3.id_t2 and t3.id = t1.id
		order by n
		for xml path('')
	) as txt
from
	t1
7 авг 13, 23:21    [14678484]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
trhrerthretherthret
Guest
для одноциферных


для id одинаковой длины :)
7 авг 13, 23:23    [14678494]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
Empirical
Member

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

alter table tab1 add ids1 int, ids2 int, ids3 etc.
update tab1 set ids1 = substring(ids,1,charindex(',',ids),2), ids2 = substring(ids,charindex(',',ids),len(ids1)+2) etc
Или функцию написать, с талли тейбл с кастомным делимитером. Потом cross apply.
8 авг 13, 01:13    [14678819]     Ответить | Цитировать Сообщить модератору
 Re: Помощь в оптимизации запроса  [new]
aleksey_sql
Member

Откуда: Калуга
Сообщений: 145
wqefgqowiuefgiwe
aleksey_sql,

вот такой гамнакодец у меня получился (принципиально не хотелось пилить вспомогательные функции). работает при условии что id в table1 одноциферные. для многоциферных ( > 9) прийдется пилить функцию, скорее всего.

;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
rt(n) as (select row_number() over(order by (select 0)) from l4 t1, l4 t2),
t1(id,ids) as
(
	select 1, '1,2,3,4' union all
	select 2, '1,4,3,2' union all
	select 3, '1,3,4,3,2'
),
t2(id, name) as
(
	select 1,'расцветали' union all
	select 2,'яблони' union all
	select 3,'и' union all
	select 4,'груши'
),
t3 as
(
	select *, substring(ids, (n - 1) * 2 + 1, 1) as id_t2
	from
		t1
			cross apply
		(
			select top(LEN(REPLACE(
							  REPLACE(
								REPLACE(
								  REPLACE(
									REPLACE(
									  REPLACE(
										REPLACE(
										  REPLACE(
											REPLACE(
											  REPLACE(
												ids,
											  '9', ''),
											'8', ''),
										  '7', ''),
										'6', ''),
									  '5', ''),
									'4', ''),
								  '3', ''),
								'2', ''),
							  '1', ''),
							'0', '')) + 1
					  ) n
			from rt
		) as q
)
select
	t1.id,
	(
		select name + ' '
		from
			t2
				inner join
			t3 on t2.id = t3.id_t2 and t3.id = t1.id
		order by n
		for xml path('')
	) as txt
from
	t1
8 авг 13, 08:23    [14679063]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
aleksey_sql
Member

Откуда: Калуга
Сообщений: 145
круто, однако:)
Спасибо за содействие!!!
Но хотелось бы что-то более универсальное...
8 авг 13, 08:23    [14679065]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
Гость333
Member

Откуда:
Сообщений: 3683
aleksey_sql
кто-нибудь сталкивался с такой организацией данных? помогите написать запросик:)

Я сталкивался. Организация данных называется "нарушение первой нормальной формы". Сначала бы нормализовать, а потом уже запросики писать :)
8 авг 13, 08:47    [14679130]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
aleks2
Guest
create function dbo.GetIt(@ids nvarchar(1024))
returns nvarchar(1024)
as
begin
  set @ids = @ids + N',';
  select @ids = replace(@ids, cast(t1.id as nvarchar(10))+N',', t1.name+N' ' ) from dbo.t1;
  return @ids;
end;
go

create table dbo.t1 (id int primary key clustered, name nvarchar(1024));
insert t1
select 1, N'расцветали'
union all
select 2, N'яблони'
union all
select 3, N'и'
union all
select 4, N'груши'


declare @t2 table(id int primary key clustered, ids nvarchar(1024));
insert @t2
select 1, N'1,2,3,4'
union all
select 2, N'1,4,3,2'
union all
select 3, N'1,3,4,3,2'

select dbo.GetIt(ids) from @t2
go
drop table dbo.t1
drop function dbo.GetIt
8 авг 13, 09:08    [14679192]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
create table  #t1 (id int primary key clustered, name nvarchar(1024));
insert #t1
select 1, N'расцветали'
union all
select 2, N'яблони'
union all
select 3, N'и'
union all
select 4, N'груши'

create table #t2 (id int primary key clustered, ids nvarchar(1024));
insert #t2
select 1, N'1,2,3,4'
union all
select 2, N'1,4,3,2'
union all
select 3, N'1,3,4,3,2'

;with result (id,ids,n) as 
(select 
 t2.id
,STUFF(t2.ids,1,len(cast(t1.id as varchar(10)))+1,t1.name+' ') as ids
,len(t1.name)+1 
from #t2 t2
join #t1 t1 on t2.ids like CAST(t1.id as varchar(10))+'%'
union all
select 
 t2.id
,STUFF(t2.ids,t2.n+1,len(cast(t1.id as varchar(10)))+1,t1.name+' ') as ids
,t2.n+len(t1.name)+1 
from result t2
join #t1 t1 on substring(t2.ids,t2.n+1,1000) like CAST(t1.id as varchar(10))+'%'
)
select top 1 with ties id,ids from result
order by ROW_NUMBER()over(partition by id order by n desc)
8 авг 13, 13:31    [14680978]     Ответить | Цитировать Сообщить модератору
 Re: Не могу составить запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
На вскидку: 10610915
Где-то я, кажется, и с таблицей замен запрос писал. А найти опять не могу!
Но приджойнить-то и в указанном решении можно запросто.
8 авг 13, 14:08    [14681202]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить