Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Правильно соединить таблицы.  [new]
?
Guest
Есть к примеру список изделий.
Это будет Таблица1.
Изделие состоит из деталей.
Это будет в Таблице2.
Каждая Деталь может содержать или не содержать резисторы и их составляющие из Таблицы3.
Каждая Деталь может содержать или не содержать транзиасторы и их составляющие из Таблицы4.
Каждая Деталь может содержать или не содержать термопары и их составляющие из Таблицы5.

Надо собрать всю информацию о составляющих входящих в изделие.

если сделать Left join всех таблиц по их связи с деталью, то если, например Таблица3 содержит 10 записей, Таблица4 - 5, а Таблица5 - 3, то исходный результат содержит 10 записей, но колонки приносящие информацию из таблиц 4 и 5 мульциплицируют ряды, а мне надо чтобы были была только нужная информация и а остальное NULL s.
Как это правильно сделать?
10 мар 13, 04:45    [14031043]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Так что ли?
with 
Изделие as (select * from(values(1,'Изд1'),(2,'Изд2'))t(id,name))
,Деталь as (select * from(values(1,1,'Дет1'),(1,2,'Дет2'),(2,1,'Дет1-2'),(2,3,'Дет3'))t(id_izd,id,name))
,Резисторы as (select * from(values(1,1,'Рез1'),(1,2,'Рез2'),(1,3,'Рез3'),(2,1,'Рез1-2'),(3,4,'Рез4'))t(id_det,id,name))
,Транзисторы as (select * from(values(1,1,'Тр1'),(3,3,'Тр3'),(3,1,'Тр1-2'))t(id_det,id,name))
,Термопары as (select * from(values(3,1,'ТП1'),(3,2,'ТП2'),(3,3,'ТП3'),(3,4,'ТП4'))t(id_det,id,name))
select i.name Изд, d.name Дет, isnull(u.rname,'')Резист, isnull(u.tname,'')Транзист,isnull(u.pname,'')Термопары
from Изделие i
join Деталь d on d.id_izd=i.id
join (
   select 
      ISNULL(ISNULL(r.id_det,t.id_det),p.id_det)id_det, 
      r.rn, r.id rid, r.name rname, 
      t.tn, t.id tid, t.name tname, 
      p.pn, p.id pid, p.name pname
   from (select *, row_number()over(partition by id_det order by id)rn from Резисторы)r
   full join(select *, row_number()over(partition by id_det order by id)tn from Транзисторы)t on t.id_det=r.id_det and t.tn=r.rn
   full join(select *, row_number()over(partition by id_det order by id)pn from Термопары)p on p.id_det=isnull(r.id_det,p.id_det) and p.pn=isnull(t.tn,r.rn)
   )u on u.id_det=d.id
order by i.id, d.id, isnull(isnull(u.rn,u.tn),u.pn)
ИздДетРезистТранзистТермопары
Изд1Дет1Рез1Тр1
Изд1Дет1Рез2
Изд1Дет1Рез3
Изд1Дет2Рез1-2
Изд2Дет1-2Рез1Тр1
Изд2Дет1-2Рез2
Изд2Дет1-2Рез3
Изд2Дет3Рез4Тр1-2ТП1
Изд2Дет3Тр3ТП2
Изд2Дет3ТП3
Изд2Дет3ТП4
10 мар 13, 10:44    [14031212]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
on p.id_det=isnull(r.id_det,p t.id_det)
10 мар 13, 10:57    [14031219]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
?
Guest
А если база не поддерживает ни over , ни partition, как сделать?
10 мар 13, 18:01    [14032060]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
?
А если база не поддерживает ни over , ни partition, как сделать?


Задать вопрос в профильном "базе" форуме.
10 мар 13, 18:09    [14032087]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
?,

Надо бд перепроектировать, применяя для деталей отношение подкатегории.

Грубо говоря, обобщить, деталь состоит из компонент, которые либо транзистор, либо резистор, либо еще что-то.
10 мар 13, 18:15    [14032112]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
?
Guest
А здесь не SQL Server?
У нас старенький, а эти функции, на сколько я понимаю, поддерживаются начиная с 2005.
10 мар 13, 18:18    [14032116]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
?
Guest
MasterZiv
?,

Надо бд перепроектировать, применяя для деталей отношение подкатегории.

Грубо говоря, обобщить, деталь состоит из компонент, которые либо транзистор, либо резистор, либо еще что-то.

Но потом-же их всё-равно надо свести в одну деталь, а потом в одно изделие.
Собственно то что я тут называю резисторы, транзисторы .... это не одна таблица а группа таблиц сведённых именно таким образом.
10 мар 13, 18:33    [14032158]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
?
А если база не поддерживает ни over , ни partition, как сделать?
там используется нумерация строк. Посмотрите FAQ про нумерацию строк для SQL 2000
10 мар 13, 19:32    [14032352]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
?
Guest
Cygapb-007
?
А если база не поддерживает ни over , ни partition, как сделать?
там используется нумерация строк. Посмотрите FAQ про нумерацию строк для SQL 2000

А вот это не понятно,
для чего мне нужна физическая нумерация строк, если у каждой записи есть уникальный ключ по таблице и все таблицы связаны по этому ключу?
10 мар 13, 19:42    [14032399]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
?
Cygapb-007
пропущено...
там используется нумерация строк. Посмотрите FAQ про нумерацию строк для SQL 2000

А вот это не понятно,
для чего мне нужна физическая нумерация строк, если у каждой записи есть уникальный ключ по таблице и все таблицы связаны по этому ключу?
То есть результат, приведенный в таблице, вас не устраивает? Ну тогда ... джойнте по-своему...
А еще лучше ... порешайте задачки на SQL-EX.RU
10 мар 13, 21:03    [14032618]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
для SQL 2000, с развернутым результатом, не скрывающим механику работы:
+ без over, partition
-- drop table #Деталь, #Изделие, #Резисторы, #Термопары, #Транзисторы
if OBJECT_ID('tempdb..#Изделие','U') is null begin
   create table #Изделие (id int, name varchar(100)) insert #Изделие values(1,'Изд1'),(2,'Изд2')
   create table #Деталь (id_izd int,id int,name varchar(100)) insert #Деталь values(1,1,'Дет1'),(1,2,'Дет2'),(2,1,'Дет1-2'),(2,3,'Дет3')
   create table #Резисторы(id_det int,id int,name varchar(100)) insert #Резисторы values(1,1,'Рез1'),(1,2,'Рез2'),(1,3,'Рез3'),(3,4,'Рез4')
   create table #Транзисторы (id_det int,id int,name varchar(100)) insert #Транзисторы values(1,1,'Тр1'),(3,3,'Тр3'),(3,1,'Тр1-2')
   create table #Термопары (id_det int,id int,name varchar(100)) insert #Термопары values(3,1,'ТП1'),(3,2,'ТП2'),(3,3,'ТП3'),(2,4,'ТП4')
end
select *--i.name Изд, d.name Дет, isnull(u.rname,'')Резист, isnull(u.tname,'')Транзист,isnull(u.pname,'')Термопары
from #Изделие i
join #Деталь d on d.id_izd=i.id
join (
   select 
      ISNULL(ISNULL(r.id_det,t.id_det),p.id_det)id_det, 
      r.rn, r.id rid, r.name rname, 
      t.tn, t.id tid, t.name tname, 
      p.pn, p.id pid, p.name pname
   from 
      (select *, (select count(*) from #Резисторы where id_det=r.id_det and id<=r.id)rn from #Резисторы r)r
   full join
      (select *,(select count(*) from #Транзисторы where id_det=t.id_det and id<=t.id) tn from #Транзисторы t)t 
         on t.id_det=r.id_det and t.tn=r.rn
   full join
      (select *, (select count(*) from #Термопары where id_det=p.id_det and id<=p.id)pn from #Термопары p)p 
         on p.id_det=isnull(r.id_det,t.id_det) and p.pn=isnull(r.rn,t.tn)
   )u on u.id_det=d.id
order by i.id, d.id, isnull(isnull(u.rn,u.tn),u.pn)
idnameid_izdidnameid_detrnridrnametntidtnamepnpidpname
1Изд111Дет1111Рез111Тр1NULLNULLNULL
1Изд111Дет1122Рез2NULLNULLNULLNULLNULLNULL
1Изд111Дет1133Рез3NULLNULLNULLNULLNULLNULL
1Изд112Дет22NULLNULLNULLNULLNULLNULL14ТП4
2Изд221Дет1-2111Рез111Тр1NULLNULLNULL
2Изд221Дет1-2122Рез2NULLNULLNULLNULLNULLNULL
2Изд221Дет1-2133Рез3NULLNULLNULLNULLNULLNULL
2Изд223Дет3314Рез411Тр1-211ТП1
2Изд223Дет33NULLNULLNULL23Тр322ТП2
2Изд223Дет33NULLNULLNULLNULLNULLNULL33ТП3
10 мар 13, 23:24    [14032957]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
?
MasterZiv
?,

Надо бд перепроектировать, применяя для деталей отношение подкатегории.

Грубо говоря, обобщить, деталь состоит из компонент, которые либо транзистор, либо резистор, либо еще что-то.

Но потом-же их всё-равно надо свести в одну деталь, а потом в одно изделие.
Собственно то что я тут называю резисторы, транзисторы .... это не одна таблица а группа таблиц сведённых именно таким образом.


Еще раз.
У тебя проблема в том, что много связей 1: N от главной таблицы изделия.

Если сделать подкатегории, то связок тебя будет одна : изделие -(1: N)- компонент,
А от компонента будет связь 1: 0..1 к одному из видов компонента, и таким образом строки не будут множится, потому что там только в одной дочерней таблице будет запись.
11 мар 13, 01:20    [14033234]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
MasterZiv
?
пропущено...Но потом-же их всё-равно надо свести в одну деталь, а потом в одно изделие.
Собственно то что я тут называю резисторы, транзисторы .... это не одна таблица а группа таблиц сведённых именно таким образом.
Еще раз.
У тебя проблема в том, что много связей 1: N от главной таблицы изделия.

Если сделать подкатегории, то связок тебя будет одна : изделие -(1: N)- компонент,
А от компонента будет связь 1: 0..1 к одному из видов компонента, и таким образом строки не будут множится, потому что там только в одной дочерней таблице будет запись.
Согласен. Отдельно список компонентов. Отдельно список связей. Сейчас в схеме ID детали содержит как описание детали, так и привязку к изделию, что неверно. Точно так же ID транзистора содержит описание параметров транзистора и связь с деталями, в которых он используется. Что тоже неверно.

Однако это только предположения, потому как схема базы не представлена :)
11 мар 13, 01:50    [14033276]     Ответить | Цитировать Сообщить модератору
 Re: Правильно соединить таблицы.  [new]
?
Guest
Спасибо большое за помощь.
Схема не представлена потому, что то что описано, очень упрощённый вариант, на самом деле
каждая упомянутая таблица это уже сведённая таблица из 5-7 других.
Добавила ID изделия в резисторы, транзисторы и термопары, через ID детали.
Насколько я поняла то что посоветовал MasterZiv, вроде работает.

Cygapb-007
вы привели замечательный пример, у меня проблема только в том, что в этих таблицах иногда более 30 колонок, замучаешься описывать, а так обязательно воспользуюсь, когда выборка поменьше будет.
11 мар 13, 03:25    [14033299]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить