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

Откуда:
Сообщений: 6
Есть схема некой установки (в приложении-картинке рассматриваемый пример схемы)
На ней есть отдельные детали:
"Гайка (в сборе)" состоит из:
- поз.394 - "Гайка" - 1шт. (длина 0м)
- поз.395 - "Врезное кольцо" - 1шт. (длина 0м)
"Тройник (в сборе)" состоит из:
- поз.402 - "Тройник" - 1шт. (длина 0м)
- поз.403 - "Гайка (в сборе)" - 3шт. (длина 0м)
"Уголок (в сборе)" состоит из:
- поз.406 - "Уголок" - 1шт. (длина 0м)
- поз.407 - "Гайка (в сборе)" - 3шт. (длина 0м)

И сама схема т.н. "Обвязка" состоит из:
- поз.1 - "Гайка (в сборе)" - 3шт. (длина 0м)
- поз.2 - "Труба" - 2шт. по L1м
- поз.3 - "Тройник (в сборе)" - 2шт. (длина 0м)
- поз.4 - "Труба" - 1шт. по L2м
- поз.5 - "Труба" - 2шт. по L3м
- поз.6 - "Тройник (в сборе)" - 1шт. (длина 0м)
- поз.7 - "Уголок (в сборе)" - 1шт. (длина 0м)

Нужно посчитать, сколько в "Обвязке" отдельных деталей, т.е.:
- "Труба" = 2шт. по L1м + 1шт. по L2м + 2шт. по L3м = 10м (количество штук не важно, если у детали указана "Длина")
- "Тройник" = 3шт.
- "Гайка" = 14шт.
- "Врезное кольцо" = 14шт.
- "Тройник (в сборе)" = 3шт.
- "Гайка (в сборе)" = 14шт.
- "Уголок (в сборе)" = 1шт.
- "Уголок" = 1шт.

Вывод списка отдельных деталей у меня получился без проблем.
Не получается подсчёт количества используемых деталей...

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

--Создание таблиц
create table TVers ( --Таблица Версий схем (необходима, т.к. версий схем-картинок в базе несколько)
ID_Vers int --Уникальный номер записи Версии
, Name_Vers varchar(20)) --Наименование Версии

create table TDetal ( --Таблица с перечнем всех деталей, имеющихся (не обязательно имеющихся на схеме-картинке)
ID_Det int --Уникальный номер записи Детали
, Name_Det varchar(20)) --Наименование Детали

create table TSpecif ( --Таблица Спецификации имеющихся деталей на схеме
ID_Specif int --Уникальный номер записи Спецификации
, Vers int --Ссылка на ID_Vers Версии
, Det int --Ссылка на ID_Det Детали
, ID_SostDetal int --Ссылка на ID_Det составной деталь (в какую входит отдельным элементом)
, KolVo int --Количество единичных деталей под одним номером
, LenDet real --Длина одной единичной детали
, LenAllDet real --Длина всех единичных деталей под одним номером
)

--Заполнение таблиц
INSERT INTO TVers (ID_Vers, Name_Vers) --Таблица Версий схем-картинок
VALUES (1,'V1')
, (2,'V2')
, (3,'V3')
INSERT INTO TDetal (ID_Det, Name_Det) --Таблица всех Деталей
VALUES (1,'Гайка')
, (2, 'Врезное Кольцо')
, (3, 'Тройник')
, (4, 'Гайка (в сборе)')
, (5, 'Тройник (в сборе)')
, (6, 'Труба')
, (7, 'Уголок')
, (8, 'Уголок (в сборе)')
INSERT INTO TSpecif (ID_Specif, Vers, Det, ID_SostDetal, KolVo, LenDet, LenAllDet) --Таблица Спецификация по схеме-картинке
VALUES (396, 1, 1, 4, 1, 0, 0) --1шт. "Гайка" в "Гайка (в сборе)"
, (397, 1, 2, 4, 1, 0, 0) --1шт. "ВрезКольцо" в "Гайка (в сборе)"

, (404, 1, 3, 5, 1, 0, 0) --1шт. "Тройник" в "Тройник (в сборе)"
, (405, 1, 4, 5, 3, 0, 0) --3шт. "Гайки (в сборе)" в "Тройник (в сборе)"

, (406, 1, 7, 8, 1, 0, 0) --1шт. "Уголок" в "Уголок (в сборе)"
, (407, 1, 4, 8, 2, 0, 0) --2шт. "Гайки (в сборе)" в "Тройник (в сборе)"

, (1, 1, 4, Null, 3, 0, 0) --3шт. "Гайка (в сборе)" в Обвязке
, (2, 1, 6, Null, 2, 1, 0) --2шт. по L1м "Труба" в Обвязке
, (3, 1, 5, Null, 2, 0, 0) --2шт. "Тройник (в сборе)" в Обвязке
, (4, 1, 6, Null, 1, 2, 0) --1шт. по L2м "Труба" в Обвязке
, (5, 1, 6, Null, 2, 3, 0) --2шт. по L3м "Труба" в Обвязке
, (6, 1, 5, Null, 1, 0, 0) --1шт. "Тройник (в сборе)" в Обвязке
, (7, 1, 8, Null, 1, 0, 0) --1шт. "Уголок (в сборе)" в Обвязке

/*
--Вывод созданных таблиц (для проверки можно разблокировать)
select
"ID_Vers" = ID_Vers
, "Name_Vers" = Name_Vers
FROM TVers
select
"ID_Det" = ID_Det
, "Name_Det" = Name_Det
FROM TDetal
select
"ID_Specif" = ID_Specif
, "Vers" = Vers
, "ID_Det" = ID_Det
FROM TSpecif
*/

--Калькуляция внутри Спецификации
update tt1
SET tt1.LenAllDet = tt1.KolVo * tt1.LenDet --Подсчёт длины всех единичных деталей под одним номером
FROM TSpecif tt1

--Выборка уникальных Версий и Деталей (в какой версии находятся какие детали) и попытка подсчёта количества
SELECT
[Ссылка на Версию] = t1.Vers
, [Ссылка на Деталь] = (select Name_Det from TDetal tt1 where t1.Det = tt1.ID_Det)
, [Общее Количество] = SUM(KolVo)
, [Общая Длина] = SUM(LenAllDet)
/*, [Кол-во Ссылочных номеров] = --Попытка подсчёта, неудачная
(select SUM(isnull(tt2.KolVo,0))
from TSpecif tt2 where t1.ID_SostDetal = tt2.Det)
*/
From TSpecif t1
GROUP BY t1.Vers, t1.Det

--Удаление используемых таблиц
DROP TABLE TVers, TDetal, TSpecif


К сообщению приложен файл. Размер - 29Kb
19 окт 18, 18:43    [21709445]     Ответить | Цитировать Сообщить модератору
 Re: Подсчёт количества во взаимосвязанных таблицах  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
ИгорьНекто,

все красиво но так и не понял что вы хотели получить в [Кол-во Ссылочных номеров]
и откройте для себя join пригодится.

SELECT
  [Ссылка на Версию] = t1.Vers
, [Ссылка на Деталь] = Name_Det
, [Общее Количество] = SUM(KolVo)
, [Общая Длина] = SUM(LenAllDet)
/*, [Кол-во Ссылочных номеров] = --Попытка подсчёта, неудачная? [b]а что хотите получить?[/b]
  (select SUM(isnull(tt2.KolVo,0))
    from TSpecif tt2 where t1.ID_SostDetal = tt2.Det)
*/    
From TSpecif t1
join TDetal tt1 on t1.Det = tt1.ID_Det

GROUP BY t1.Vers, t1.Det, Name_Det
19 окт 18, 19:53    [21709506]     Ответить | Цитировать Сообщить модератору
 Re: Подсчёт количества во взаимосвязанных таблицах  [new]
ИгорьНекто
Member

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

благодарю за "JOIN" -постараюсь применять в дальнейшем.
Тот заблокированный блок в скрипте поля "[Кол-во Ссылочных номеров]" - прошу вовсе не рассматривать.
А получить хотел следующее:
Ссылка на Деталь         Общее количество     Общая длина     Кол-во Ссылочных номеров
Гайка 1 0 14
ВрезКольцо 1 0 14
Тройник 1 0 3
Гайка (в сборе) 8 0 14
Тройник (в сборе) 3 0 3
Труба 5 10 5
Уголок 1 0 1
Уголок (в сборе) 1 0 1
19 окт 18, 20:14    [21709513]     Ответить | Цитировать Сообщить модератору
 Re: Подсчёт количества во взаимосвязанных таблицах  [new]
ИгорьНекто
Member

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

на всякий случай поясню, почему "Гайка (в сборе)" именно 14.
В "Обвязке" - 3шт. отдельных "Гайка (в сборе)" (поз.1): 3гайки = 3
Ещё в каждом тройнике (поз.3, 6) по 3 гайки: 3тройника * 3гайки = 9.
В уголке (поз.7) 2 гайки: 1уголок * 2гайки = 2.
Всего: 3 + 9 + 2 = 14шт.
Ну и в каждой "Гайке (в сборе)" есть по 1 "Гайке" и 1 "Врезное кольцо".
Следовательно:
Кол-во "Гайка" = 14шт., и "Врезное кольцо" = 14шт.
19 окт 18, 20:29    [21709529]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить