Помогите составить запрос / Firebird, InterBase

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите составить запрос

11 сообщений из 11, страница 1 из 1

Помогите составить запрос

#39051220

Marcello

Гость

Здравствуйте!

Есть несколько таблиц.

Группы:

Код: sql

1.
2.
3.
4.

CREATE TABLE TOVAR_CATEGORY (
    TC_ID        BIGINT NOT NULL,
    TC_NAME      VARCHAR(100) NOT NULL
);

Товары:

Код: sql

1.
2.
3.
4.
5.
6.

CREATE TABLE TOVAR (
    TV_ID             BIGINT NOT NULL,
    TV_ART            VARCHAR(50) NOT NULL,
    TV_ID_CAT         BIGINT
);
ALTER TABLE TOVAR ADD CONSTRAINT FK_TOVAR_2 FOREIGN KEY (TV_ID_CAT) REFERENCES TOVAR_CATEGORY (TC_ID);

Атрибуты:

Код: sql

1.
2.
3.
4.
5.
6.
7.

CREATE TABLE EXTRA_PROPS (
    EP_ID        BIGINT NOT NULL,
    EP_OBJ       BIGINT,
    EP_NAME      VARCHAR(128) NOT NULL,
    EP_ORDER     INTEGER
);
ALTER TABLE EXTRA_PROPS ADD CONSTRAINT FK_EXTRA_PROPS_2 FOREIGN KEY (EP_OBJ) REFERENCES TOVAR_CATEGORY (TC_ID);

Значения атрибутов:

Код: sql

1.
2.
3.
4.
5.
6.

CREATE TABLE EXTRA_VALUES (
    EV_ID           BIGINT NOT NULL,
    EV_ID_EP        BIGINT NOT NULL,
    EV_NAME         VARCHAR(128) NOT NULL
);
ALTER TABLE EXTRA_VALUES ADD CONSTRAINT FK_EXTRA_VALUES_2 FOREIGN KEY (EV_ID_EP) REFERENCES EXTRA_PROPS (EP_ID);

Связи товаров со значениями атрибутов:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

CREATE TABLE EXTRA_VALUES_REF (
    REF_ID    BIGINT NOT NULL,
    REF_OBJ1  BIGINT NOT NULL,
    REF_EV    BIGINT NOT NULL,
    REF_EP    BIGINT
);
ALTER TABLE EXTRA_VALUES_REF ADD CONSTRAINT FK_EXTRA_VALUES_REF_2 FOREIGN KEY (REF_OBJ1) REFERENCES TOVAR (TV_ID);
ALTER TABLE EXTRA_VALUES_REF ADD CONSTRAINT FK_EXTRA_VALUES_REF_3 FOREIGN KEY (REF_EV) REFERENCES EXTRA_VALUES (EV_ID);
ALTER TABLE EXTRA_VALUES_REF ADD CONSTRAINT FK_EXTRA_VALUES_REF_4 FOREIGN KEY (REF_EP) REFERENCES EXTRA_PROPS (EP_ID);

Товары объединяются в документы в таблице

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

CREATE TABLE REF_DD (
    REF_ID            BIGINT NOT NULL,
    REF_OBJ1          BIGINT NOT NULL,
    REF_OBJ2          BIGINT NOT NULL,
    REF_COUNT         NUMERIC(15,3)
);
ALTER TABLE REF_DD ADD CONSTRAINT FK_REF_DD_2 FOREIGN KEY (REF_OBJ1) REFERENCES DOCUMENT (DC_ID);
ALTER TABLE REF_DD ADD CONSTRAINT FK_REF_DD_3 FOREIGN KEY (REF_OBJ2) REFERENCES TOVAR (TV_ID);

Чтобы получить количество товара в разрезе групп использую запрос:

Код: sql

1.
2.
3.
4.
5.
6.

select tc.tc_id, tc.tc_name, sum(dd.ref_count)
from ref_dd dd
left join tovar tv on tv.tv_id = dd.ref_obj2
left join tovar_category tc on tc.tc_id = tv.tv_id_cat
where dd.ref_obj1 = <ID_DOC>
group by 1, 2

Для разбивки результата по значениям атрибутов составил запрос так:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

select tc.tc_id, tc.tc_name, ev.ev_id, ev.ev_name, sum(dd.ref_count)
from ref_dd dd
left join tovar tv on tv.tv_id = dd.ref_obj2
left join tovar_category tc on tc.tc_id = tv.tv_id_cat
left join extra_values_ref rex on rex.ref_obj1 = tv.tv_id
left join extra_values ev on ev.ev_id = rex.ref_ev
where dd.ref_obj1 = <ID_DOC>
group by 1, 2, 3, 4

Из-за множественных связей в EXTRA_VALUES_REF суммирование происходит некорректно. Подскажите, как нужно спроектировать таблицы EXTRA_PROPS, EXTRA_VALUES, EXTRA_VALUES_REF, чтоб избежать проблем с суммированием? В каждой группе товаров может быть неограниченное число атрибутов. В каждом атрибуте - неограниченное число значений.

...

Рейтинг:

0 / 0

14.09.2015, 20:30

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051277

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

я бы для начала выкинул LEFT там где он не нужен.

Не вижу где в последнем запросе используется EXTRA_PROPS

...

Рейтинг:

0 / 0

14.09.2015, 22:38

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051371

Добрый Э - Эх

Гость

Marcello,

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

Select * 
  from t1
  join (
         select sum(...) as summ, count(1) as cnt 
           from t2
          group by <список полей группировки>
       ) V
    on v.<поле для связи с таблицей T1> = t1.<поле для связи с таблицей T2>

...

Рейтинг:

0 / 0

15.09.2015, 07:14

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051430

Marcello

Гость

Симонов ДенисMarcello,

я бы для начала выкинул LEFT там где он не нужен.

Не вижу где в последнем запросе используется EXTRA_PROPS
Эту таблицу опустил в примере, т.к. не она влияет на ошибочное суммирование. В таблице EXTRA_PROPS есть поле EP_ORDER, которое указывает на очередность (важность) атрибутов. По моей идее пользователь должен иметь возможность выбирать атрибуты, включаемые в отчет. Поэтому запрос набирается динамически и столбцы в TcxGridTableView, соответствующие очередности атрибутов, тоже формируются динамически. Для указания очередности и используется таблица EXTRA_PROPS. Запрос двух атрибутов (3-го и 5-го) выглядит так:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.

select tc.tc_id, tc.tc_name, 

iif(ep5.ep_order = 3, ev3.ev_id, 0) as ev_id3, 
iif(ep5.ep_order = 3, ev3.ev_name, null) as ev_name3, 

iif(ep5.ep_order = 5, ev5.ev_id, 0) as ev_id5, 
iif(ep5.ep_order = 5, ev5.ev_name, null) as ev_name5, 

sum(dd.ref_count)

from ref_dd dd
left join tovar tv on tv.tv_id = dd.ref_obj2
left join tovar_category tc on tc.tc_id = tv.tv_id_cat

left join extra_values_ref rex3 on rex3.ref_obj1 = tv.tv_id
left join extra_values ev3 on ev3.ev_id = rex3.ref_ev
left join extra_props ep3 on ep3.ep_id = ev3.ev_id_ep

left join extra_values_ref rex5 on rex5.ref_obj1 = tv.tv_id
left join extra_values ev5 on ev5.ev_id = rex5.ref_ev
left join extra_props ep5 on ep5.ep_id = ev5.ev_id_ep

where dd.ref_obj1 = <ID_DOC>

group by tc.tc_id, tc.tc_name, 
iif(ep5.ep_order = 3, ev3.ev_id, 0), 
iif(ep5.ep_order = 3, ev3.ev_name, null), 
iif(ep5.ep_order = 5, ev5.ev_id, 0), 
iif(ep5.ep_order = 5, ev5.ev_name, null)

...

Рейтинг:

0 / 0

15.09.2015, 08:59

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051434

Marcello

Гость

Если в секцию where последнего запроса добавить условие отбора значений атрибутов, то суммирование выполняется корректно

Код: sql

where dd.ref_obj1 = <ID_DOC> and ev3.ev_id in (...) and ev5.ev_id in (...)

Т.е. пользователю нужно уточнить конкретные значения атрибутов для отчета. Получить отчет без уточнения, просто в виде распределения по атрибутам, не получается.

...

Рейтинг:

0 / 0

15.09.2015, 09:08

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051460

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

Убери присоединение таблицы на каждый атрибут. Присоединяй её только один раз и тогда у тебя будет нормальный результат.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

select tc.tc_id, tc.tc_name, 

  iif(ep.ep_order = 3, ev.ev_id, 0) as ev_id3,
  iif(ep.ep_order = 3, ev.ev_name, null) as ev_name3,

  iif(ep.ep_order = 5, ev.ev_id, 0) as ev_id5,
  iif(ep.ep_order = 5, ev.ev_name, null) as ev_name5,

  sum(dd.ref_count)

from ref_dd dd
left join tovar tv on tv.tv_id = dd.ref_obj2
left join tovar_category tc on tc.tc_id = tv.tv_id_cat

left join extra_values_ref rex on rex.ref_obj1 = tv.tv_id
left join extra_values ev on ev.ev_id = rex.ref_ev
left join extra_props ep on ep.ep_id = ev.ev_id_ep

where dd.ref_obj1 = <ID_DOC>

group by
  tc.tc_id, tc.tc_name,
  iif(ep.ep_order = 3, ev.ev_id, 0),
  iif(ep.ep_order = 3, ev.ev_name, null),
  iif(ep.ep_order = 5, ev.ev_id, 0),
  iif(ep.ep_order = 5, ev.ev_name, null)

...

Рейтинг:

0 / 0

15.09.2015, 09:31

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051527

FedorinoFF

Участник

Сообщения: 114

Рейтинг: 0 / 0

Симонов ДенисMarcello,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

select 
  ...
group by
  tc.tc_id, tc.tc_name,
  iif(ep.ep_order = 3, ev.ev_id, 0),
  iif(ep.ep_order = 3, ev.ev_name, null),
  iif(ep.ep_order = 5, ev.ev_id, 0),
  iif(ep.ep_order = 5, ev.ev_name, null)

Ой , Ё, group by в каком непривычном виде...

...

Рейтинг:

0 / 0

15.09.2015, 10:17

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051572

Marcello

Гость

Симонов ДенисMarcello,

Убери присоединение таблицы на каждый атрибут. Присоединяй её только один раз и тогда у тебя будет нормальный результат.
Теперь результат рисуется в шахматном порядке. Вместо одной строки со значениями А и Б получаю 2 строки: А - [пусто], [пусто] - Б.

...

Рейтинг:

0 / 0

15.09.2015, 10:40

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051630

Симонов Денис

Участник

Откуда: Рязань

Сообщения: 11 624

Рейтинг: 0 / 0

Marcello,

ну а чего ты от group by ждёшь. Ты сначала без агрегата sum попробуй настроить запрос так чтобы он по каждому товару давал набор требуемых свойств. Обверни этот запрос в CTE а потом поверх него уже группировку делай

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

with tprop as (
  select
    rex.ref_obj1 as tv_id,
    /* нужные свойства */
    max(iif(ep.ep_order = 3, ev.ev_id, 0)) as ev_id3,
    max(iif(ep.ep_order = 3, ev.ev_name, null)) as ev_name3,

    max(iif(ep.ep_order = 5, ev.ev_id, 0)) as ev_id5,
    max(iif(ep.ep_order = 5, ev.ev_name, null)) as ev_name5
  from extra_values_ref rex
    left join extra_values ev on ev.ev_id = rex.ref_ev
    left join extra_props ep on ep.ep_id = ev.ev_id_ep
  group by 1
)
select
  tc.tc_id,
  tc.tc_name,

  tprop.ev_id3,
  tprop.ev_name3,

  tprop.ev_id5,
  tprop.ev_name5,

  sum(dd.ref_count)

from ref_dd dd
left join tovar tv on tv.tv_id = dd.ref_obj2
left join tovar_category tc on tc.tc_id = tv.tv_id_cat
left join tprop on tprop.tv_id =tprop. tv_id

where dd.ref_obj1 = <ID_DOC>

group by
  tc.tc_id,
  tc.tc_name,

  tprop.ev_id3,
  tprop.ev_name3,

  tprop.ev_id5,
  tprop.ev_name5

...

Рейтинг:

0 / 0

15.09.2015, 11:29

| Ответить | Цитировать | Написать

Помогите составить запрос

#39051679

Marcello

Гость

Симонов ДенисMarcello,

ну а чего ты от group by ждёшь. Ты сначала без агрегата sum попробуй настроить запрос так чтобы он по каждому товару давал набор требуемых свойств. Обверни этот запрос в CTE а потом поверх него уже группировку делай

Спасибо, получилось!

...

Рейтинг:

0 / 0

15.09.2015, 12:15

| Ответить | Цитировать | Написать

Помогите составить запрос

#39052043

WildSery

Участник

Откуда: да, оттуда.

Сообщения: 19 151

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

15.09.2015, 16:41

| Ответить | Цитировать | Написать

11 сообщений из 11, страница 1 из 1

Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Помогите составить запрос

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=40&msg=39051430&tid=1562628]:	0ms
get settings:	7ms
get forum list:	11ms
check forum access:	4ms
check topic access:	4ms
track hit:	44ms
get topic data:	10ms
get forum data:	2ms
get page messages:	42ms
get tp. blocked users:	1ms
others:	15ms

total:	140ms