sql: как выбрать последний документ продажи по каждой позиции номенклатуры?

Bulat Ziganshin
Дата: 04.12.2007 13:39:59
у меня вопрос по SQL, но как ни странно на этом форуме нет чистого sql-раздела :D

используются delphi7, ado, access (точнее, ms jet)

вопрос вот какой: есть стандартная база автоматизации торговли - номенклатура, документы, список номенклатуры в каждом документе. мне нужно для каждой позиции номенклатуры найти последний (по doc_date) документ, в котором она упоминается. желательно одним sql-запросом, иначе это будет слишком медленно. и то ли я не знаю sql, то ли он для этого не приспособлен?

отобрать самую свежую дату продажи по каждой позиции я могу:

select nid_id, max(doc_date)
from nomenclature join nomenclature_in_documents ..
group by nid_id
а вот как вытащить остальную информацию из этого документа?

может, это можно сделать с помощью сортировки и last? типа

select nid_id, last(doc_date), last(doc_num)
from nomenclature join nomenclature_in_documents ..
group by nid_id
order by nid_id, doc_date
Gerasimenko
Дата: 04.12.2007 13:43:25
select top 1 *  
from nomenclature join nomenclature_in_documents ..
order by nid_id, doc_date DESC

Naf
Дата: 04.12.2007 13:45:27
Не привели структуры Вашей БД
Bulat Ziganshin
Дата: 04.12.2007 14:08:47
Gerasimenko
select top 1 *  
from nomenclature join nomenclature_in_documents ..
order by nid_id, doc_date DESC


так я получу только одну запись. а мне нужен посл. док-т по каждой позиции номенклатуры

>Не привели структуры Вашей БД

стандартная для такого типа задач:

documents - primary key doc_id, имена полей doc_*
nomenclature_in_documents - primary key nid_id, имена полей nid_*

documents и nomenclature_in_documents связаны 1:N по критерию doc_id=nid_document

в таблице nomenclature_in_documents есть поле nid_nomenclature, по которому мне нужно сгруппировать записи

в таблице documents есть поле doc_date, и мне нужно в каждой группе отобрать записи с максимальным её значением (любую строку если их будет несколько одинаковых)

т.е. на несуществующем диалекте sql это могло бы выглядеть как

select nid_nomenclature, top 1 documents.*  
from documents join nomenclature_in_documents ..
order by nid_nomenclature, doc_date DESC
group by nid_nomenclature

- сгруппировать по nid_nomenclature и выбрать в каждой группе запись с максимальным doc_date
softwarer
Дата: 04.12.2007 14:10:12
Bulat Ziganshin
у меня вопрос по SQL, но как ни странно на этом форуме нет чистого sql-раздела :D

Как нет в природе и чистого SQL :)

Bulat Ziganshin
мне нужно для каждой позиции номенклатуры найти последний (по doc_date) документ, в котором она упоминается. желательно одним sql-запросом, иначе это будет слишком медленно. и то ли я не знаю sql, то ли он для этого не приспособлен?

"Старый стандартный SQL" действительно плохо подходит для этого. Идите в форум по используемой БД и спрашивайте, как это сделать быстро именно в ее случае. Может, аналитические функции, может, еще чего. Кроме того, подумайте над тем, чтобы заменить max (doc_date) на max (doc_id) - если контекст позволяет, прилинковать документ станет делом техники.
Naf
Дата: 04.12.2007 14:19:10
select nid_id,doc_id,doc_*
from documents
right join 
 ( --тут ваш запрос "отобрать самую свежую дату продажи по каждой позиции"
select nid_id, max(doc_date) max_date
from nomenclature join nomenclature_in_documents ..
group by nid_id) on (max_date=doc_date)
Gerasimenko
Дата: 04.12.2007 14:25:02
Bulat Ziganshin
Gerasimenko
select top 1 *  
from nomenclature join nomenclature_in_documents ..
order by nid_id, doc_date DESC


так я получу только одну запись. а мне нужен посл. док-т по каждой позиции номенклатуры

>Не привели структуры Вашей БД

стандартная для такого типа задач:

documents - primary key doc_id, имена полей doc_*
nomenclature_in_documents - primary key nid_id, имена полей nid_*

documents и nomenclature_in_documents связаны 1:N по критерию doc_id=nid_document

в таблице nomenclature_in_documents есть поле nid_nomenclature, по которому мне нужно сгруппировать записи

в таблице documents есть поле doc_date, и мне нужно в каждой группе отобрать записи с максимальным её значением (любую строку если их будет несколько одинаковых)

т.е. на несуществующем диалекте sql это могло бы выглядеть как

select nid_nomenclature, top 1 documents.*  
from documents join nomenclature_in_documents ..
order by nid_nomenclature, doc_date DESC
group by nid_nomenclature

- сгруппировать по nid_nomenclature и выбрать в каждой группе запись с максимальным doc_date

Это всего лишь DESC, а использовать его можно и во встроенном селекте и в join
Bulat Ziganshin
Дата: 04.12.2007 14:28:51
Naf
select nid_id,doc_id,doc_*
from documents
right join 
 ( --тут ваш запрос "отобрать самую свежую дату продажи по каждой позиции"
select nid_id, max(doc_date) max_date
from nomenclature join nomenclature_in_documents ..
group by nid_id) on (max_date=doc_date)



select documents.*
from documents join nomenclature_in_documents ...
join (select nid_nomenclatureas max_nomenclature, max(doc_date) as max_date
       from documents join nomenclature_in_documents ...
       group by nid_nomenclature) 
on max_date=doc_date and nid_nomenclature=max_nomenclature

видимо где-то так. а ведь я себя считал спецом по sql! :D

спасибо, Naf!
Bulat Ziganshin
Дата: 04.12.2007 14:31:25
Gerasimenko
Это всего лишь DESC, а использовать его можно и во встроенном селекте и в join


а применительно к данной задаче есть какие-то идеи? фишка-то в том, что top нужен по каждой группе и я что-то не могу сообразить, как такое делается (без отдельных запросов по каждой позиции, пусть и через параметры)
Bulat Ziganshin
Дата: 04.12.2007 14:35:08
softwarer
Кроме того, подумайте над тем, чтобы заменить max (doc_date) на max (doc_id) - если контекст позволяет, прилинковать документ станет делом техники.


для данной задачи это означает, что через полгода они начнут искать почему отчёт изредка сбоит LOL

путём курения бубна я нашёл ещё одно извратное решение:

select ..., max(doc_date & doc_num & ....)