Задачи для обучения SQL-запросам в Супермаге : Программирование

█ 27.10.2014 08:27

Всё хорошо. Только продажник убъет тебя за список этих значений.
Нужна общая сумма по каждому артикулу.

█ 30.10.2014 16:47

насмотрелся Мирончик И. видива. он говорит мол Decode() лучше не юзать, так как это старая функция и она ресурсоемкая, лучше юзать case() вот переписал вот так.

Код:

select sp.article, sp.doctype, sp.docid, 
                             case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end as "Количество", sp.totalprice

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and s.createdat between '&date1' and '&date2'
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')

добавил общую сумму по артикулу - sp.totalprice думаю как отнять теперь 8))

█ 30.10.2014 16:54

Отнимать также. Только я не в этом смысле сумму имела ввиду...
Я просила сумму всех количеств.

█ 11.11.2014 16:12

Код:

select sum(quantity) from 
(select sp.article, sp.doctype, sp.docid, 
                             case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end as quantity, sp.totalprice

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and s.createdat between '&date1' and '&date2'
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')) p

не могу всунуть в один запрос, вечно ругается: ORA - 00937 групповая функция не является одногппной.

Код:

select  sp.article, sp.doctype, sp.docid, 
                             case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end as quantity, sp.totalprice, sum(quantity)

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and s.createdat between '&date1' and '&date2'
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')

█ 12.11.2014 07:26

Окончательный вариант мне не нравится.
Где там в результате артикул товара?

Для групповых функций добавляются еще условия группировки.
Причем, по большому счету, туда надо добавлять все поля, к которым не применяются групповые операции:
Только по артикулам:

Код:

select  sp.article, 
                             sum(case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end ) as quantity,
 sum(case 
                              when trim(sp.doctype) = 'CS' then sp.totalprice
                               when trim(sp.doctype) = 'CR' then -sp.totalprice
                                end)  totalprice

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and s.createdat between '&date1' and '&date2'
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')
group by sp.article

По артикулам и местам хранения:

Код:

select  sp.article, decode(sp.doctype,'CS', sp.locationfrom, sp.locationto),
                             sum(case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end ) as quantity,
 sum(case 
                              when trim(sp.doctype) = 'CS' then sp.totalprice
                               when trim(sp.doctype) = 'CR' then -sp.totalprice
                                end)  totalprice

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and s.createdat between '&date1' and '&date2'
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')
group by sp.article, decode(sp.doctype,'CS', sp.locationfrom, sp.locationto)

Предлагаю теперь из этого набора отобрать товары, которых продали за период меньше 5 штук, например.

█ 12.11.2014 11:46

Код:

select  sp.article, 
                             sum(case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end ) as quantity,
                             sum(case 
                              when trim(sp.doctype) = 'CS' then sp.totalprice
                               when trim(sp.doctype) = 'CR' then -sp.totalprice
                                end)  as totalprice

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and (s.createdat between '&date1' and '&date2' and quantity < '&quantity')
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')
group by sp.article

█ 12.11.2014 11:50

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

█ 12.11.2014 12:02

а разве

Код:

sum(case 
when trim(sp.doctype) = 'CS' then sp.quantity 
when trim(sp.doctype) = 'CR' then -sp.quantity 
end ) as quantity

не суммарные продажи позиции?
group by article

█ 12.11.2014 12:08

Псевдонимы запроса нельзя использовать условиях в запросов.
Здесь название совпало с названием одного из полей используемых таблиц (причем оно уникально в пределах этих таблиц) и поэтому запрос работает.
Но работает так, как написал Андрей: под quantity понимается sp.quantity, и на него накладывается условие.
Предлагаю всё же погуглить про группировки в запросах.

█ 12.11.2014 12:25

добавил в подзапрос

Код:

select p.article, p.quant, p.total_$ from 

(select  sp.article, 
                             sum(case 
                              when trim(sp.doctype) = 'CS' then sp.quantity 
                               when trim(sp.doctype) = 'CR' then -sp.quantity 
                                end ) as quant,
                             sum(case 
                              when trim(sp.doctype) = 'CS' then sp.totalprice
                               when trim(sp.doctype) = 'CR' then -sp.totalprice
                                end)  as total_$

from smspec sp, smcard crd, smdocuments s

where sp.doctype IN ('CS', 'CR')
and s.id=sp.docid
and sp.doctype = s.doctype
and (s.createdat between '&date1' and '&date2')
and sp.article = crd.article
and crd.idclass in (select idclass from sacardclass where tree like '1.%')
group by sp.article) p

where p.quant < 5
order by 1

не уверен, что верно, так как за подзапросы вечно отгребаю....