Как получить сводные данные и данные столбца из базы данных Derby, объединяющей три таблицы

Мне нужно представить данные из базы данных Derby в JTable, но два столбца представляют собой совокупные суммы из двух связанных таблиц «один ко многим». Вот пример схемы:

SHIFTDATA:
    ID
    DATE
    SHIFT
    FOOD_COST
    OFFICE_SUPPLIES
    REP_MAINT
    NET_SALES
    SALES_TAX

OTHERPAIDOUTS:
    ID
    SHIFTDATA_ID
    LABEL
    AMOUNT

DISCOUNTS
    ID
    SHIFTDATA_ID
    DISCOUNT_NAME
    AMOUNT

Существует 0 или более OTHERPAIDOUTS для данной SHIFTDATA.

Есть 0 или более СКИДОК для данной SHIFTDATA

Мне нужен эквивалент этого оператора, хотя я знаю, что не могу комбинировать агрегатные выражения с «неагрегатными выражениями» в операторе SELECT:

SELECT (S.FOOD_COST + S.OFFICE_SUPPLIES + S.REP_MAINT + SUM(O.AMOUNT)) AS TOTAL_PAIDOUTS, 
SUM(D.AMOUNT) AS TOTAL_DISCOUNT, 
S.NET_SALES,
S.SALES_TAX
FROM SHIFTDATA S, OTHERPAIDOUTS O, DISCOUNTS D WHERE O.SHIFTDATA_ID=S.ID AND D.SHIFTDATA_ID=S.ID

Я вижу в других потоках, где добавление предложения GROUP BY исправляет эти ситуации, но я думаю, что добавление второго агрегата из третьей таблицы сбивает меня с толку. Я пробовал GROUP BY S.NET_SALES, S.SALES_TAX и добавлял AND S.ID = 278 к предложению WHERE, чтобы получить известный результат, и TOTAL_PAIDOUTS был правильным (в OTHERPAIDOUTS есть 3 связанные записи), но возвращаемый TOTAL_DISCOUNTS равен в 3 раза больше, чем должно быть.

Излишне говорить, что я не программист SQL! Надеюсь, вы уловили суть того, что я ищу. Я пробовал вложенные операторы SELECT, но просто запутался. Это приложение все еще находится в разработке, включая структуру базы данных, поэтому, если другая структура БД упростит ситуацию, это может быть вариантом. Или, если есть другой способ программного построения модели таблицы, я также открыт для этого. Заранее спасибо!!

======== Редактировать =============

Чтобы проверить значения из известной записи, я запрашиваю определенный SHIFTDATA.ID. Ниже приведены примеры записей таблицы:

SHIFTDATA:
ID  |FOOD_COST |OFFICE_SU&|REP_MAINT |NET_SALES |SALES_TAX
------------------------------------------------------
278 |0.00      |5.00      |10.00     |3898.78   |319.79

OTHERPAIDOUTS:
ID         |SHIFTDATA_&|LABEL                                   |AMOUNT
---------------------------------------------------------------------------
37         |278        |FOOD COST FUEL                          |52.00
38         |278        |MAINT FUEL                              |5.00
39         |278        |EMPLOYEE SHOES                          |21.48

DISCOUNTS:
ID         |ITEM_NAME                               |SHIFTDATA_&|AMOUNT
---------------------------------------------------------------------------
219        |Misc Discounts                          |278        |15.91

Что я ожидаю увидеть для этой строки SHIFTDATA в JTable:

TOTAL_PAIDOUTS | TOTAL_DISCOUNT |NET_SALES |SALES_TAX
------------------------------------------------------
93.48          |15.91           |3898.78   |319.79

Лучшее, что я могу получить, это добавить предложение GROUP BY, но группируя по полям из SHIFTDATA, я получаю:

TOTAL_PAIDOUTS | TOTAL_DISCOUNT |NET_SALES |SALES_TAX
------------------------------------------------------
93.48          |47.73           |3898.78   |319.79

java derby swing
person RobG    schedule 04.12.2018    source источник
comment
Можете ли вы привести пример данных входной таблицы и выходных данных, которые будут отображаться в JTable?   -  person prasad_    schedule 04.12.2018
comment
Я отредактирую свой ОП, так как у меня ограничена длина комментария.   -  person RobG    schedule 04.12.2018
comment
Конечно,. Вы можете добавить его внизу вашего поста!   -  person prasad_    schedule 04.12.2018
comment
Как вы пришли к значению 93.48 для TOTAL_PAIDOUTS?   -  person prasad_    schedule 04.12.2018
comment
Это результат S.FOOD_COST + S.OFFICE_SUPPLIES + S.REP_MAINT + SUM(O.AMOUNT) AS TOTAL_PAIDOUTS   -  person RobG    schedule 04.12.2018
comment
Что такое SUM(O.AMOUNT)?   -  person prasad_    schedule 04.12.2018
comment
Это то, что используется в исходном операторе SELECT, СУММА поля AMOUNT из таблицы OTHERPAIDOUTS. Эта часть запроса работает нормально — если я добавляю GROUP BY S.FOOD_COST, S.OFFICE_SUPPLIES, S.REP_MAINT, — но СУММА DISCOUNTS.AMOUNT умножается на 3, предположительно из-за трех строк из OTHERPAIDOUTS. Спасибо за интерес! Я в конце своей смены (у меня сейчас 06:00), поэтому я не буду проверять до 20:00 по моему времени)   -  person RobG    schedule 04.12.2018

Ответы (2)


arrow_upward
1
arrow_downward

Вот SQL-запрос с требуемым результатом.

Вот определения таблиц, данные, sql и результаты:

CREATE TABLE shiftdata (
    id int,
    foodcost int,
    officesuppl int,
    repmaint int,
    netsales int,
    salestax int);

CREATE TABLE otherpaidouts (
    id int,
    shiftid int,
    label varchar(20),
    amount int);

CREATE TABLE discounts (
    id int,
    shiftid int,
    itemname varchar(20),
    amount int);

Создайте данные для двух смен: 278 и 333. У обеих смен есть discounts, но только у 278 смены есть otherpaidouts.

insert into shiftdata values (278, 0, 5, 10, 3898, 319);
insert into shiftdata values (333, 22, 15, 100, 2111, 88);
insert into otherpaidouts values (37, 278, 'Food Cost FUEL', 52);
insert into otherpaidouts values (38, 278, 'Maint FUEL', 5);
insert into otherpaidouts values (39, 278, 'Empl SHOES', 21);
insert into discounts values (219, 278, 'Misc DISCOUNTS', 15);
insert into discounts values (312, 333, 'Misc DISCOUNTS', 25);


Запрос:

SELECT sd.id, sd.netsales, sd.salestax,
  IFNULL(
    (SELECT SUM(d.amount) FROM discounts d WHERE d.shiftid=sd.id), 0) AS total_discount,
  (SELECT sd.foodcost + sd.officesuppl + sd.repmaint + IFNULL(SUM(op.amount), 0) FROM otherpaidouts op WHERE op.shiftid=sd.id) AS total_paidouts
FROM shiftdata sd;


Результат:

+------+----------+----------+----------------+----------------+
| id   | netsales | salestax | total_discount | total_paidouts |
+------+----------+----------+----------------+----------------+
|  278 |     3898 |      319 |             15 |             93 |
|  333 |     2111 |       88 |             25 |            137 |
+------+----------+----------+----------------+----------------+
person prasad_    schedule 05.12.2018
comment
Мужчина. Выглядит очень хорошо, но я не вижу функции IFNULL в Derby. - person RobG; 05.12.2018
comment
Мужчина. Я знал, что ты на что-то наткнулся. Что я нашел, так это функцию COALESCE, которая работает точно так же! Ты сделал это! Очень, ОЧЕНЬ благодарен! Вы проделали огромную работу, большое спасибо. - person RobG; 05.12.2018
comment
Да, я думал о MYSQL (у MySQL есть COALESCE)! Да, я немного работал с Apache Derby (он же JavaDB), но спасибо, что указали на это. Большинство сообщений, связанных с базой данных на SO, относятся к MySQL, и я не заметил, что это был Derby. - person prasad_; 05.12.2018

arrow_upward
1
arrow_downward

Попробуйте LEFT OUTER JOIN примерно так:

SELECT S.FOOD_COST + S.OFFICE_SUPPLIES + S.REP_MAINT + SUM(O.AMOUNT) AS TOTAL_PAIDOUTS, 
       SUM(D.AMOUNT) AS TOTAL_DISCOUNT, 
       S.NET_SALES,
       S.SALES_TAX
FROM SHIFTDATA S
LEFT JOIN OTHERPAIDOUTS AS O ON O.SHIFTDATA_ID = S.ID
LEFT JOIN DISCOUNTS AS D     ON D.SHIFTDATA_ID = S.ID

Редактировать

SELECT S.FOOD_COST + S.OFFICE_SUPPLIES + S.REP_MAINT +
       ( SELECT COALESCE(SUM(AMOUNT), 0) FROM OTHERPAIDOUTS WHERE SHIFTDATA_ID = S.ID ) AS TOTAL_PAIDOUTS,
       ( SELECT COALESCE(SUM(AMOUNT), 0) FROM DISCOUNTS     WHERE SHIFTDATA_ID = S.ID ) AS TOTAL_DISCOUNT,
       S.NET_SALES,
       S.SALES_TAX
FROM SHIFTDATA S
person Usagi Miyamoto    schedule 04.12.2018
comment
Спасибо, но при этом я получаю сообщение Когда список SELECT содержит хотя бы один агрегат, все записи должны быть допустимыми агрегатными выражениями. До сих пор все вопросы, которые я видел о смешивании агрегатов с неагрегатами, говорили о том, что должно быть включено предложение GROUP BY. Единственное предложение GROUP BY, которое я вообще могу заставить работать, — это список имен отдельных полей (S.FOOD_COST, S.OFFICE_SUPPLIES и т. д.), а сумма TOTAL_DISCOUNT — это фактическая сумма, умноженная на количество записей OTHERPAIDOUTS для каждой SHIFTDATA. - person RobG; 04.12.2018
comment
Вот и все! Спасибо! - person RobG; 05.12.2018
comment
Проверь это. Этот запрос возвращает правильные результаты, если SUM(AMOUNT) FROM OTHERPAIDOUTS не равно нулю. Если он равен нулю, я все равно не получаю сумму столбцов SHIFTDATA. - person RobG; 05.12.2018