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

Откуда:
Сообщений: 115
Всем добрый день!
Сижу и туплю как написать запрос, чтобы выводил в таблицу и отсутствующие позиции
Т.е есть определенная номенклатура товара, но она не всегда в наличии.
труба 630 стенка 8
труба 630 стенка 9
труба 630 стенка 10
труба 630 стенка 11
труба 630 стенка 12
Если в базе есть только некоторые позиции, то соответственно запрос выведет только их.
Диаметр стенка длина Характеристика Цена
630 9.00 139.73 п/ш 40000
630 10.00 22.57 п/ш 39000

Можно, конечно создать записи с нолями, но не хочется захламлять бд

а хотелось бы видеть таблицу что то типа такой

Диаметр стенка длина Характеристика Цена
630 8.00 под заказ н/д запрос цены
630 9.00 139.73 п/ш 40000
630 10.00 22.57 п/ш 39000
630 11.00 под заказ н/д запрос цены
630 12.00 под заказ н/д запрос цены
6 ноя 19, 10:42    [22010524]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
mdm114
Можно, конечно создать записи с нолями, но не хочется захламлять бд
Интересно, а когда товар уходит, вы удаляете все связанные с этим товаром записи, например, историю продаж, возвраты и т.д.?

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

Тогда программирование будет лёгким и приятным, а база - прозрачной и понятной.
6 ноя 19, 10:55    [22010535]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
982183
Member

Откуда: VL
Сообщений: 3211
Очевидно, у вас есть таблица с ассортиментом и таблица с остатками, возможно таблица с ценами.
Left Join вам в руки.

А без описания структуры БД советовать нечего
6 ноя 19, 10:57    [22010539]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
982183
Очевидно, у вас есть таблица с ассортиментом и таблица с остатками, возможно таблица с ценами.
Как я понял, у автора нету таблицы товаров.
Есть предполагаемые товары - трубы, с диаметром "от" и "до", и с каким то шагом, и таблица остатков, притом если остаток 0, то запись удаляется.
Идентификаторов товара тоже нет, если что :-)

По моему, так даже курсовик делать трудно, с таким подходом, а о реальной системе я вообще молчу.
6 ноя 19, 11:20    [22010571]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
Спасибо за ответы, структура примерно такая:

На каждый товар заводится карточка с описанием. Диаметр, стенка, состояние, объект, цены и проч. Соответственно это таблица с описанием. Есть связанная таблица(наличие товара), которая описывает длину, тоннаж, характеристики и качество каждой позиции в отдельности. При продаже всех позиций данной карточки товара, она помечается как проданная и уходит в архив. Соответственно товары из второй таблицы(склад) перемещаются в таблицу продажи. Привязывать товары к одной и той же карточке не получится. Я уже думал в эту сторону,но специфика такова, что всегда приходится на каждую новую партию заводить новую карточку.

В сторону left joint думал, но пока не совсем получается то, что надо. Некоторые редкие позиции типа 630х12 вообще никогда не были в наличии... А в результатах запроса хотелось бы видеть и их
Мне вот тут давали советы по выборкам с учетом отсутствия данных за определенный месяц. Я думал, что можно тоже какой то сет сделать с номенклатурой товара и исходя из нее, привязаться к наличию той или иной позиции. Но никак не додумаю как это сделать, да и реально ли это
with a as(select 1 i union all select i+1 from a where i<12),
b as(select i,month(dateadd(month,i-12,current_timestamp))m,year(dateadd(month,i-12,current_timestamp))y from a),
c as(select m,y,s from(values(2018,8,9999),(2018,11,7777),(2019,3,2222))x(y,m,s))
select i,b.m,b.y,c.s from b left outer join c on b.y=c.y and b.m=c.m
6 ноя 19, 11:43    [22010612]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
982183
Member

Откуда: VL
Сообщений: 3211
А товар однотипный?
Трубы разного диаметра и разными стенками всё?
6 ноя 19, 11:50    [22010621]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
982183
Member

Откуда: VL
Сообщений: 3211
alexeyvg
По моему, так даже курсовик делать трудно, с таким подходом, а о реальной системе я вообще молчу.

Вчера только с соседнего офиса люди за помощью пришли.
У них на 1С 6,0 !!!!!!! склад написан.
На ключе контакты окислились - ПО не запускалось....
15 лет работали без сопровождения...
6 ноя 19, 11:53    [22010626]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
mdm114
Спасибо за ответы, структура примерно такая:

На каждый товар заводится карточка с описанием. Диаметр, стенка, состояние, объект, цены и проч. Соответственно это таблица с описанием. Есть связанная таблица(наличие товара), которая описывает длину, тоннаж, характеристики и качество каждой позиции в отдельности. При продаже всех позиций данной карточки товара, она помечается как проданная и уходит в архив. Соответственно товары из второй таблицы(склад) перемещаются в таблицу продажи. Привязывать товары к одной и той же карточке не получится. Я уже думал в эту сторону,но специфика такова, что всегда приходится на каждую новую партию заводить новую карточку.

В сторону left joint думал, но пока не совсем получается то, что надо. Некоторые редкие позиции типа 630х12 вообще никогда не были в наличии... А в результатах запроса хотелось бы видеть и их
А, теперь стало понятнее.

Как я понял, ваша карточка товара - это поставка.
То есть, если есть труба 630х9, то будет несколько карточек, на каждую поставку, правильно?

Так и надо сделать справочник номенклатуры, там будет одна запись для 630х9
Далее делаются записи в таблицу поставок (по мере совершения таковых), там будет мнонго записей, ссылающихся на эту 630х9
А потом уже делать движения по складам, и остатки по складам.

И запрос будет очень простой, с двумя лефт-джойнами:
from Номенклатура
    left join Поставка
    left join Остатки
6 ноя 19, 12:04    [22010644]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
Немного не так
1 таблица, немного упрощенно
id Диаметр стенка характеристикипоставщик ВладелецЦена Вх.цена итд
2236309.00п/ш Оренбургреализация2500023000
22463010.00п/ш ВоронежСтальсобственная2500023000

2 таблица
длина состояние пр. пометкирезерв№ карточки
11.65 лежалая--нет223
11.64 лежалая--нет223
11.63 лежалая--нет223
11.62 лежалая--нет224
11.61 лежалая--нет224
11.60 лежалая--нет224


В запросе соответственно суммируются данные по каждой карточке и выводится только общий метраж и тоннаж
982183
А товар однотипный?
Трубы разного диаметра и разными стенками всё?

Да, однотипный. Только вот эти позиции
труба 630 стенка 8
труба 630 стенка 9
труба 630 стенка 10
труба 630 стенка 11
труба 630 стенка 12
6 ноя 19, 14:38    [22010781]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
alexeyvg

Так и надо сделать справочник номенклатуры, там будет одна запись для 630х9
Далее делаются записи в таблицу поставок (по мере совершения таковых), там будет мнонго записей, ссылающихся на эту 630х9
А потом уже делать движения по складам, и остатки по складам.
[/src]

При продаже карточка закрывается, так как следующая поставка аналогичного товара(630х9) может быть с другими характеристиками, начиная от поставщика и заканчивая ценами, поэтому нет смысла привязываться только к одной.
6 ноя 19, 14:43    [22010790]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
mdm114
Немного не так
Да, я понял, как у вас.

Вашу "1 таблица" я назвал "таблица поставок".
Может, это не очень точно.

Можно назвать её "таблица товаров", или "Номенклатура", и сделать ещё таблицу "Типоразмеры"

Тогда получится так (если, например, труба 630,10.00 пришла от 2х разных поставщиков):
1 таблица ("Типоразмеры")
id Диаметр стенка характеристики
126309.00п/ш
1363010.00п/ш


2 таблица ("Номенклатура", то есть конкретный товар конкретного поставщика)
id id Типоразмеры поставщик ВладелецЦена Вх.цена итд
22312Оренбургреализация2500023000
22413ВоронежСтальсобственная2500023000
22513Оренбургсобственная2500023000


3 таблица (остатки на складе)
длина состояние пр. пометкирезерв№ карточки
11.65 лежалая--нет223
11.64 лежалая--нет223
11.63 лежалая--нет223
11.62 лежалая--нет224
11.61 лежалая--нет224
11.60 лежалая--нет224
11.61 лежалая--нет225
11.60 лежалая--нет225


mdm114
При продаже карточка закрывается, так как следующая поставка аналогичного товара(630х9) может быть с другими характеристиками, начиная от поставщика и заканчивая ценами, поэтому нет смысла привязываться только к одной.
Да, вот это я учёл в своём варианте модели

Сообщение было отредактировано: 6 ноя 19, 14:56
6 ноя 19, 14:55    [22010807]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
спасибо, тоже уже думаю в сторону третьей таблицы с типоразмерами
6 ноя 19, 15:11    [22010824]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
домучил все таки вот такой вариант. Еще, правда немного корявый. Надо будет немного доработать и додумать какие значения убрать, а так вроде все правильно выводит

  with a as(select 7 i union all select i+1 from a where i<11),
b as(select i, ('630')Diam,(i+1)ST from a),
c as(select diam, st,dl,price
from(
SELECT table1.posId, table1.diam, table1.st, SUM(table2.dl) AS dl
FROM     table1 INNER JOIN   table2 ON...
GROUP BY ....
) AS t)
	 
select i,b.Diam,b.ST,coalesce(c.diam,0)Diam_table1,coalesce(c.st,0)St_table2,coalesce(c.dl,0)Длина,coalesce(c.price,0)Цена
from b left outer join c on b.Diam=c.diam and b.ST=c.st


i Diam ST DiamDb StDb Длина Цена
1 630 8 0 0.00 0.0000 0
2 630 9 630 9.00 139.7300 40000
2 630 9 630 9.00 1120.7600 42000
3 630 10 630 10.00 22.5700 39000
4 630 11 0 0.00 0.0000 0
5 630 12 0 0.00 0.0000 0


Может подскажет кто, возможно ли изменить конструкцию
coalesce(c.price,0)Цена
чтобы в результате вывода был не 0, а "под заказ"
6 ноя 19, 16:07    [22010882]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
982183
Member

Откуда: VL
Сообщений: 3211
Ну сколько раз тут обсуждали уже структуру данных складского учета.
А извращение начинаются при вот таких нестандартных ситуациях.
Если всего пять номенклатурных позиций, то это не повод отказываться от самой этой сущности.
Даже если дополнительные характеристики порождают некое подобие новых номенклатурных позиций.
6 ноя 19, 16:10    [22010883]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
StarikNavy
Member

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

coalesce( cast(c.price as nvarchar(50)),'под зак') Цена
6 ноя 19, 16:11    [22010884]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
StarikNavy, спасибо

982183,позиций на самом деле немного больше. Просто привел для примера 5. А с таблицей "Типоразмеры" я еще попробую. Просто у меня что то с запросом на три таблицы не очень получилось
6 ноя 19, 16:25    [22010900]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
982183
Member

Откуда: VL
Сообщений: 3211
Ну так забудь про свои велосипеды.
Посмотри на "стандартные" методы реализации.
6 ноя 19, 16:29    [22010903]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
mdm114
А с таблицей "Типоразмеры" я еще попробую. Просто у меня что то с запросом на три таблицы не очень получилось
Там же простой запрос, просто два left join

Сообщение было отредактировано: 6 ноя 19, 16:53
6 ноя 19, 16:53    [22010930]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
alexeyvg, не получилось, потому что не захотелось добавлять в существующую таблицу новую колонку с ключем "id Типоразмеры". Пробовал использовать диаметр и стенку как ключи. А так согласен, что правильнее использовать три таблицы. Но дело в том, что эта таблица больше нигде не нужна будет. Хочу сделать вывод этой таблицы в виде блока на web страницу, чтобы данные не редактировать вручную
7 ноя 19, 09:16    [22011211]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
mdm114
потому что не захотелось добавлять в существующую таблицу новую колонку с ключем "id Типоразмеры". Пробовал использовать диаметр и стенку как ключи
Вообще можно сделать primary key на диаметр и стенку, и потом сделать ссылку. Ничего тут сложного нет.

Но лучше всё таки сделать нормальный целочисленный identity integer id, и добавить его в существующую таблицу.
7 ноя 19, 10:25    [22011275]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
PizzaPizza
Member

Откуда:
Сообщений: 288
Карточка товара, говорите. Надо же думать в терминах баз данных, структур и алгоритмов когда пишите базу данных со структурой данных и алгоритмами обращения с данными, а не бездумно копировать бумажные физические сущности.

Посмотрите как люди делают склады и подумайте почему именно так, а не как у вас "без справочников". Не бывает такой "специфики" когда справочник не нужен. Это в реальном мире не работает. У вас ваши же поставщики или покупатели покупают по закрытому списку позиций, который и должен быть отражен в базе.
Единственное исключение я могу придумать, если вы бутик труб и продаете эксклюзивные трубы по требованиям заказчика типа "два метра диаметром 60 мм со стенкой 5 мм, потом увеличение диаметра на 10 мм на длину 10 см и ещё два метра трубы спиралью диаметром 5 мм и стенкой 1 мм и все это с одной стороны из меди, а с другой из стали".

А вообще почитайте про архитектуру данных, про переиспользование данных, про нормальные формы и их суть, иначе такие простые задачи как получить список проданных позиций с их суммарным весом или количеством, будут вас ставить в тупик.
8 ноя 19, 07:34    [22012029]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
PizzaPizza
Member

Откуда:
Сообщений: 288
В вашем случае справочник это пересечение двух простейших таблиц: одной со всеми возможными диаметрами и другой со всеми толщинами.
Места занимает мизер, будет лежать скорее всего в памяти, заодно можно использовать как ограничитель/список скачков пресетов, что бы пользователи не пихали нереальные опции типа диаметр 10 мм со стенкой 9 мм.
8 ноя 19, 07:43    [22012032]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
PizzaPizza
В вашем случае справочник это пересечение двух простейших таблиц: одной со всеми возможными диаметрами и другой со всеми толщинами.
Места занимает мизер, будет лежать скорее всего в памяти, заодно можно использовать как ограничитель/список скачков пресетов, что бы пользователи не пихали нереальные опции типа диаметр 10 мм со стенкой 9 мм.
Если "посмотреть, как люди делают склады" (с), то лучше сделать просто справочник с размерами, без всяких пересечений.
Потому что нет бизнес сущности "диаметр" или "толщина", есть выпускаемые трубной индустрией размеры ("стандартизованные", или "принятые в отрасли"). Из чего следует, что реальная линейка размеров труб не будет в точности соответствовать произведению множеств "диаметр" и "толщина".
8 ноя 19, 08:58    [22012064]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
mdm114
Member

Откуда:
Сообщений: 115
PizzaPizza
Карточка товара, говорите...

Это я так для простоты выразился, чтобы понятнее было
PizzaPizza
У вас ваши же поставщики или покупатели покупают по закрытому списку позиций, который и должен быть отражен в базе....
....Единственное исключение я могу придумать, если вы бутик труб и продаете эксклюзивные трубы по требованиям заказчика...

Не совсем так, но очень похоже. Торговля идет не новым а переработанным сырьем.
Приведу пример той спецификации, о которой Вы говорите
Диаметрстенка ГОСТсталь цена
630720295 17Г1С4000
630820295 17Г1С4000
630920295 17Г1С4000
6301020295 17Г1С4000
720820295 17Г1С4000
720920295 17Г1С4000
7201020295 17Г1С4000
7201220295 17Г1С4000

Тут все просто
в моем случае выглядит примерно так
Диаметрстенкасостояние фаскипоперечный шов среда использования цена
6307бу нетесть вода 20000
6307восстановленная данет газ 39000
6307восстановленная даестьгаз 40000
6307восстановленная даестьнефть 24000
6307.5восстановленная даестьнефть 24000
6307.5лежалая данет -- 34000
6308лежалая данет-- 44000
6308лежалая данет-- 44000
6309собственное производство данет-- 44000
63010собственное производство данет-- 44000

Смысла нет подключать номенклатурный справочник в том виде, про который вы говорите или делать свой. Поэтому есть отдельная таблица со спецификацией каждой партии товара(или как я выразился словом, которое всех коробит "карточка")
Может со стороны и немного заморочено выглядит, но на практике все работает в лучшем виде. Причем, что больше всего нравится - это отчетность по каждой партии товара. Можно смотреть какой товар и какого состояния быстрее продается, а какой задерживаются на складе. И, характеристики в виде стенок и диметра здесь не основные.

Как уже писал выше, тот запрос мне нужен лишь для одной цели - обобщенного вывода лишь нескольких позиций на сайт. Например из вышеуказанной таблицы нужно вытащить только две записи "собственное производство", а вместо остальных поставить "нет в наличии". Пример таблицы был выше. В принципе я уже понял в какую сторону двигаться, Сейчас разбираюсь с ключами, чтобы не добавлять в существующую таблицу лишнюю колонку..
8 ноя 19, 09:39    [22012105]     Ответить | Цитировать Сообщить модератору
 Re: Вывод отсутствующих позиций в таблицу  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29549
mdm114
Смысла нет подключать номенклатурный справочник в том виде, про который вы говорите или делать свой.
...
Как уже писал выше, тот запрос мне нужен лишь для одной цели - обобщенного вывода лишь нескольких позиций на сайт.
Так и не нужен "справочник полной номенклатуры".

Речь о том, что модель данных в БД должна отражать вашу бизнес-модель, модель тех сущностей, которыми оперирует ваш бизнес (сотрудники).

Если вы говорите, что у вас есть некие "размеры", которые определённым образом понимаются сотрудниками, и используются в бизнесе компании, если вы хотите, что бы пользователь видел эти "размеры" в интерфейсе (не партии, а именно линейку размеров, к которой уже подцепляются партии, или поставки, или реальные размеры каких то заводов), то, значит, должна быть такая таблица.

Не придуманная "таблица полной номенклатуры", а таблица, которое отражает реально используемые в бизнесе сущности, является физической реализацией (в БД) этой сущности.


Вот, как то так.
А вы занимаетесь какой то ерундой, не работой с моделью данных, с бизнес моделью, а подсчётом количества колонок в таблице :-) Да хоть 100 колонок можно добавить, сервер выдержит!
mdm114
Сейчас разбираюсь с ключами, чтобы не добавлять в существующую таблицу лишнюю колонку..
8 ноя 19, 09:59    [22012119]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить