Как найти номера и даты создания последних приходных накладных (запрос)? : Супермаг Плюс (Супермаг 2000)

dmware · █ 27.09.2007 09:54

Здравствуйте!
Помогите, пожалуйста, с запросом.
Всего-то нужно найти номера и даты создания последних приходных накладных для выбранных артикулов.
Вообще говоря можно сделать так:

Код:

SELECT   SMSPEC.ARTICLE,
         SMDOCUMENTS.ID as SMDOCUMENTS_ID,
         SMDOCUMENTS.CREATEDAT as SMDOCUMENTS_CREATEDAT
FROM     SMDOCUMENTS,         
         SMSPEC,
         SLSPECPACKS
WHERE    SMDOCUMENTS.ID = SMSPEC.DOCID
         AND SLSPECPACKS.DOCID = SMSPEC.DOCID 
         AND SMSPEC.SPECITEM = SLSPECPACKS.SPECITEM
         AND SMDOCUMENTS.DOCTYPE IN('WI')
         AND SMSPEC.ARTICLE IN('0000505', '0000496', '0000545')         
ORDER BY SMDOCUMENTS.CREATEDAT DESC

и получить список документов, где они будут отсортированы по датам.
В итоге что-то вроде этого:

Код:

1	0000496	ПН64703904	20.09.2007
2	0000505	ПН64703904	12.09.2007
3	0000496	ПН25793757	30.08.2007
4	0000505	ПН25793757	30.08.2007
5	0000545	ПН02353702	23.08.2007

В примере участвуют три артикула: 0000505, 0000496, 0000545. В приведенном выше результате информация о последнем встречается только в пятой строчке. Как убрать лишние, ненужные мне строки с номерами 3, 4?
Нужного результата я могу добиться следующим запросом:

Код:

SELECT   SMSPEC_ARTICLE,
         SMDOCUMENTS_ID,
         SMDOCUMENTS_CREATEDAT
FROM     (
         SELECT   SMSPEC.ARTICLE as SMSPEC_ARTICLE,
                  SMDOCUMENTS.ID as SMDOCUMENTS_ID,
                  SMDOCUMENTS.CREATEDAT as SMDOCUMENTS_CREATEDAT/*,
         SLSPECPACKS.VALIDDATE as SLSPECPACKS_VALIDDATE
         */
         FROM     SMDOCUMENTS,         
                  SMSPEC,
                  SLSPECPACKS
         WHERE    SMDOCUMENTS.ID = SMSPEC.DOCID
                  AND SLSPECPACKS.DOCID = SMSPEC.DOCID 
                  AND SMSPEC.SPECITEM = SLSPECPACKS.SPECITEM
                  AND SMDOCUMENTS.DOCTYPE IN('WI')
                  AND SMSPEC.ARTICLE IN('0000505')                 
         ORDER BY SMDOCUMENTS.CREATEDAT DESC
         )
WHERE    ROWNUM = 1

UNION ALL
SELECT   SMSPEC_ARTICLE,
         SMDOCUMENTS_ID,
         SMDOCUMENTS_CREATEDAT
FROM     (
         SELECT   SMSPEC.ARTICLE as SMSPEC_ARTICLE,
                  SMDOCUMENTS.ID as SMDOCUMENTS_ID,
                  SMDOCUMENTS.CREATEDAT as SMDOCUMENTS_CREATEDAT/*,
         SLSPECPACKS.VALIDDATE as SLSPECPACKS_VALIDDATE
         */
         FROM     SMDOCUMENTS,         
                  SMSPEC,
                  SLSPECPACKS
         WHERE    SMDOCUMENTS.ID = SMSPEC.DOCID
                  AND SLSPECPACKS.DOCID = SMSPEC.DOCID 
                  AND SMSPEC.SPECITEM = SLSPECPACKS.SPECITEM
                  AND SMDOCUMENTS.DOCTYPE IN('WI')
                  AND SMSPEC.ARTICLE IN('0000496')                 
         ORDER BY SMDOCUMENTS.CREATEDAT DESC
         )
WHERE    ROWNUM = 1
...

Результат:

Код:

1	0000505	ПН64703904	12.09.2007
2	0000496	ПН64703904	20.09.2007
3	0000545	ПН02353702	23.08.2007

Но это решение мне не подходит, потому как предполагается выполнять запрос по группам товаров, где множество артикулов. Запрос будет просто огромным, если учесть, что все приведенное - лишь составная часть другого...
Можно ли сделать иначе?

dmware · █ 27.09.2007 10:14

Да, не упомянул, у меня там еще и ограничение по месту хранения AND SMDOCUMENTS.LOCATIONTO IN(...), хотя применительно к вопросу это не существенно...

reddevil · █ 27.09.2007 10:28

Код:

select c.article, round(avg(c.itemprice) ,2) price, prih.maxdata, prih.loc, prih.docdate
from
(select mx.article, substr(mx.max_data, 9) maxdata
, to_date(substr(max_data,1,8),'YYYYMMDD') docdate
, mx.locationto loc
from
(SELECT    
                     s.article,
                    MAX(TO_CHAR (d.createdat, 'YYYYMMDD')||d.ID) max_data, d.locationto  
               FROM 
                    supermag.smdocuments d,
                    supermag.smspec s
              WHERE d.doctype = s.doctype
                AND d.ID = s.docid
                AND d.docstate>=2
                AND d.opcode = 0
                AND d.doctype = 'WI'
                AND d.createdat<=TO_DATE (sysdate, 'DD.MM.YY')
           GROUP BY  s.article, d.locationto) mx) prih,
           supermag.smspec c
            where prih.article=c.article
   and prih.maxdata=c.docid
   and c.doctype='WI'
group by c.article, prih.maxdata, prih.loc, prih.docdate

Условия сам навешаешь надеюсь.

akonev · █ 27.09.2007 10:52

reddevil, а зачем в три этажа? двух хватило бы.

Mtirt · █ 27.09.2007 11:23

Я всю жизнь это делаю через аналитические функции Оракла...

reddevil · █ 27.09.2007 11:31

Цитата:

Andrew_Konev ➤ reddevil, а зачем в три этажа? двух хватило бы.

Для даты ДА, а для цены?

reddevil · █ 27.09.2007 11:31

Цитата:

Mtirt ➤ Я всю жизнь это делаю через аналитические функции Оракла...

Примерчик можно?

Mtirt · █ 27.09.2007 11:39

Код:

select * from(
select t.idclass classgroup, 
       t.article article, 
       t.name name,
       a.quantity||' '||a.unitname Mesuriment, 
       a.barcode barcode, 
       last_value(d.itemprice) over (partition by d.article,a.barcode order by c.createdat desc) price, 
       c.createdat date_price,
       (select b.taxgroupid  from smcardtax b where b.dateto='01.01.9999' and b.article=t.article) taxgroup,
       row_number() over (partition by t.article,a.barcode order by c.createdat desc) row_num                      
from smcard t, smstoreunits a, smspec d,smdocuments c
where a.article=t.article
      and d.doctype='WI'
      and d.article=t.article
      and c.doctype=d.doctype
      and c.id=d.docid
      and t.accepted=1)
where row_num=1

Правда там еще много лишнего, штрих-кода, единицы измерения, ставка НДС и т.п...

dmware · █ 27.09.2007 12:10

Не перестаю удивляться гибкости SQL-я и его реализации в Оракле!
Каждый день узнаешь что-то новое.
Прочитав пример от reddevil (спасибо за помощь!), организовал сразу же вот так, и доволен:

Код:

SELECT SMSPEC_ARTICLE as MAX_SMSPEC_ARTICLE,         
MAX(SMDOCUMENTS_CREATEDAT) as MAX_SMDOCUMENTS_CREATEDAT
FROM    (
SELECT SMSPEC.ARTICLE as SMSPEC_ARTICLE,
SMDOCUMENTS.ID as SMDOCUMENTS_ID,                                        
(SMDOCUMENTS.CREATEDAT) as SMDOCUMENTS_CREATEDAT
FROM    SMDOCUMENTS, SMSPEC                                        
WHERE  SMDOCUMENTS.ID = SMSPEC.DOCID                                        
           AND SMDOCUMENTS.DOCTYPE IN('WI')
           AND SMSPEC.ARTICLE IN('0000505', '0000496', '0000545')
           AND SMDOCUMENTS.LOCATIONTO IN(1)   
GROUP BY SMSPEC.ARTICLE, 
              SMDOCUMENTS.ID,                                        
              SMDOCUMENTS.CREATEDAT
ORDER BY SMDOCUMENTS.CREATEDAT DESC
)  
GROUP BY SMSPEC_ARTICLE

Однако, вижу теперь, что на самом деле возможностей гораздо больше.