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

Откуда:
Сообщений: 11
Здравствуйте.
Имеется база данных с партномерами или артикулами запчастей для бытовой техники, многие из которых являются полными аналогами или взаимозаменяемыми.
Имеется таблица "similar", в которой прописаны взаимосвязи для некоторых пар артикулов. Значения могут повторяться как в первом, так и во втором столбце.

Например:
Таблица "similar"

art1---------------art2
6.29.0048 6.29.0044
6.29.0048 6.29.0100
6.29.0048 6.29.0152
6.29.0048 6.29.0072

6.29.0044 6.29.0048

6.29.0100 6.29.0152
6.29.0100 6.29.0164

6.29.0072 ..............
6.29.0164 ..............

Подскажите, пожалуйста, как составить запрос, чтобы при вводе пользователем некоторого артикула (например 6.29.0044), для которого на данный момент прописана всего одна взаимосвязь, получить в результате список всех уникальных номеров аналогичных запчастей из всех имеющихся взаимосвязей - то есть такой список:6.29.0044 6.29.0048 6.29.0100 6.29.0152 6.29.0072 6.29.0164, без повторений. последовательность неважна. Заранее благодарю за помощь.
20 окт 17, 16:14    [20886494]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Изучайте рекурсивное CTE.
20 окт 17, 16:26    [20886545]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

Откуда:
Сообщений: 11
Спасибо за наводку. Мысль понял. Буду пытаться изучать. Наткнулся в интернете на нечто подобное. Скажите, пожалуйста, для реализации данного запроса необходимо чтобы была так сказать прямая и обратная запись в таблице (1-2, 2-1), либо же это излишнее раздувание базы, и можно реализовать и без этого?
Картинка с другого сайта.
20 окт 17, 17:37    [20886837]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
aleks222
Guest
invm
Изучайте рекурсивное CTE.

Опять вредные советы?

Задача тредстартера не создать вселенский тормоз, а эффективную систему.

Гораздо эффективнее сгенерировать в таблице ВСЕ уникальные соответствия.

И не париться за размер таблицы.
20 окт 17, 17:42    [20886855]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

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

Конечно! Тормоза нам не нужны:) Может быть вы можете подсказать более эффективный путь, если считаете что этот не самый оптимальный?
20 окт 17, 17:50    [20886870]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
aleks222
Guest
feller
Может быть вы можете подсказать более эффективный путь, если считаете что этот не самый оптимальный?


Чукча не читатель?

сгенерировать в таблице ВСЕ уникальные соответствия.
20 окт 17, 18:03    [20886910]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

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

Сорри, не понял что это конкретный ответ) Чукча объяснит ситуацию. По большому счет чукча своими ручками не написал еще ни одного sql-запроса. Чукча знаком с программированием на среднем уровне и голова варит. Просто сих пор не работал с базами данных. То есть моя задача пока для меня довольно абстрактная. Я пытаюсь разобраться как я в будущем смогу получать из базы нужные мне данные, и исходя из этого формирую структуру для будущей базы данных. Вот... то есть мой вопрос на данном этапе - чисто теоретический. А за реализацией дело не станет. А почему я переспросил - так как не особо пока знаком с путями решения каких-то возможно типовых задач, сорри:) Вот... поэтому, я конечно, и сам разберусь через некоторое время, но был бы очень Вам благодарен, если бы набросали хотя бы "эскиз", с помощью каких операторов мне получить именно вот такие вот нужные мне уникальные соответствия, чтобы у меня в голова уже сложилась какая-то картина. Спасибо!
20 окт 17, 20:18    [20887203]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

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

Еще забыл добавить, что по аналогии с программированием, предыдущий оратор предложил использовать нечто вроде вложенных циклов. И естественно это занимает очень долгое время. А вот для вашей фразы я аналогии в программировании не нахожу, вот потому и переспрашиваю :)
20 окт 17, 20:20    [20887207]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
aleks222
Задача тредстартера не создать вселенский тормоз
Дарагуля (с), ты уж как-нибудь определись: рекурсивное CTE - это вселенский тормоз или нет? А то у тебя то да, то нет.
Причем главный критерий - кто предложил решение. Если ты, то само-собой не тормоз, а очень даже эффективно.
20 окт 17, 21:31    [20887333]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
invm
Member

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

Эффективный вариант для вашего конкретного случая определите на практике. Причем в процессе развития системы он может перестать быть таковым.

ЗЫ: При небольшой высоте дерева и наличии правильных индексов рекурсивное CTE вполне себе эффективно.
20 окт 17, 21:41    [20887353]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

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

А можете применительно к моему случаю хотя бы примерно написать как будет выглядеть запрос? с моим названиями таблицы и полей. Для составления паззла у меня в голове. Буду очень благодарен!
20 окт 17, 23:50    [20887598]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
aleks222
Guest
invm
aleks222
Задача тредстартера не создать вселенский тормоз
Дарагуля (с), ты уж как-нибудь определись: рекурсивное CTE - это вселенский тормоз или нет? А то у тебя то да, то нет.
Причем главный критерий - кто предложил решение. Если ты, то само-собой не тормоз, а очень даже эффективно.


Дарагуля, учи диалектический материализм.


-- исходная таблица
declare @similar table( art1 nvarchar(64), art2 nvarchar(64), primary key(art1, art2))

-- полная таблица соответствий (генерируется ОДИН раз, перегенерируется при изменении исходной)
declare @full table( art1 nvarchar(64), art2 nvarchar(64), primary key(art1, art2));

insert @similar
 values('6.29.0048', '6.29.0044')
, ('6.29.0048', '6.29.0100')
, ('6.29.0048', '6.29.0152')
, ('6.29.0048', '6.29.0072');

-- генерация полной таблицы

-- 1. Копируем исходную таблицу
with x as ( select * from @similar where art1 <> art2)
   , t as ( select * from @full )
  merge t using x on t.art1 = x.art1 and t.art2 = x.art2
    when not matched then insert values(art1, art2);

with x as ( select art1 = art2, art2 = art1 from @similar where art1 <> art2 )
   , t as ( select * from @full )
  merge t using x on t.art1 = x.art1 and t.art2 = x.art2
    when not matched then insert values(art1, art2);

-- 2. Генерируем все прочие соответствия
declare @rc int = 1;

while @rc > 0 begin

    with x0 as ( select * from @full )
       , x  as ( select x1.art1, x2.art2 from x0 as x1 inner join x0 as x2 on x1.art2 = x2.art1 where x1.art1 <> x2.art2)
       , t as ( select * from @full )
      merge t using x on t.art1 = x.art1 and t.art2 = x.art2
        when not matched then insert values(art1, art2);
    set @rc = @@rowcount;

end;

select * from @full;
21 окт 17, 07:23    [20887896]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

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

Спасибо огромное! Почти понятно:) и если я вам еще не надоел, ответьте, пожалуйста на несколько вопросов. Заранее извиняюсь, если они слишком банальные или глупые.

Для меня очевидно, что код, который вы написали - это процедура на языке Transact SQL. Но так как я еще вообще не отстреливаю что к чему, а результат её выполнения увидеть хочется уже сейчас, чтобы понимать, я в папке Stored Procedures создаю процедуру, копирую туда этот код и получаю кучу ошибок

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near 'merge'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'merge'.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 29
Must declare the scalar variable "@rc".
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'merge'.
Msg 137, Level 15, State 1, Line 36
Must declare the scalar variable "@rc".


Итак, вопросы следующие:
1.) как она должна быть оформлена сейчас, чтобы я смог запустить и увидеть результат (нужны ли какие-то доп. операторы - CREATE PROCEDURE и т.д.)

2.)Как привязать эти табличные переменные @similar и @full к конкретным таблицам в моей базе данных, чтобы не в тексте процедуры вставить значения в таблицу similar, а считать то, что есть в ней на данный момент?

3.) что такое merge я уже узнал, но вот во второй части первого пункта никак не могу понять что значит запрос "select art1 = art2, art2 = art1 from @similar where art1 <> art2". Выбрать art1=art2 при том что art1<>art2 ... как-то не укладывается в голове, не могу понять :)

4.)все эти x,t, x1,x2 - это переменные, которые используются без явного объявления?

5.)в случае выполнения условия "when not matched then insert values(art1, art2);" изменяется значение переменной t или самой табличной переменной @full ?

6.)"x as ( select x1.art1, x2.art2 from x0 as x1 inner join x0 as x2 on x1.art2 = x2.art1 where x1.art1 <> x2.art2)"
Можете на словах объяснить что здесь происходит? куча операторов, непонятен приоритет их выполнения, и вообще голова кругом :)

7.)И САМОЕ ГЛАВНОЕ - посоветуйте, пожалуйста, литературу - с чего начать, чтобы понять все вот это, и научиться составлять вот такие вот непонятные запросы, как в пункте №6, и больше не задавать глупых вопросов, и не отвлекать умных людей по пустякам :))

Буду Вам ОЧЕНЬ благодарен если вы несмотря на явную глупость некоторых моих вопросов потрудитесь доходчиво ответить!
21 окт 17, 17:20    [20888667]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
aleks222
Guest
Версия MS SQL какая?
21 окт 17, 17:42    [20888688]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

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

2014
21 окт 17, 17:44    [20888690]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
aleks222
Guest
Это рабочий скрипт.
На 2012 работает - на 2014 тоже должен.
21 окт 17, 17:49    [20888697]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

Откуда:
Сообщений: 11
Ну не знаю... делаю вот так:

Картинка с другого сайта.
21 окт 17, 18:39    [20888744]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36686
9.0 -- это 2005й сервер.
22 окт 17, 02:27    [20889420]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

Откуда:
Сообщений: 11
да, сорри. на компьютере оказывается был установлен 2005 сервер, и к нему и подключался. теперь все работает. но вопрос остался: как в данном скрипте присвоить переменной @similar содержимое реальной таблицы similar? и после выполнения скрипта значение переменной @full "выгрузить" в таблицу full.
22 окт 17, 13:30    [20889749]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
aleks222
Guest
Просто заменить @similar на dbo.similar.
С full также.
22 окт 17, 15:49    [20889954]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3436
feller
да, сорри. на компьютере оказывается был установлен 2005 сервер, и к нему и подключался. теперь все работает. но вопрос остался: как в данном скрипте присвоить переменной @similar содержимое реальной таблицы similar? и после выполнения скрипта значение переменной @full "выгрузить" в таблицу full.


;-)
22 окт 17, 17:48    [20890071]     Ответить | Цитировать Сообщить модератору
 Re: Выбор всех взаимосвязанных значений из двух столбцов  [new]
feller
Member

Откуда:
Сообщений: 11
Ролг Хупин,

И что не так в этой фразе?
22 окт 17, 19:21    [20890201]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить