MYSQL рассчитывает сумму из двух таблиц?

У меня есть 2 таблицы mysql:

Таблица «Заказы»:

customer_id | money
3             120
5             80
3             45
3             70
6             20

«сборный» стол:

customer_id | money
3             50
3             70
4             20
4             90

Я хочу получить такой результат:

Таблица «Итого»:

customer_id | Amount
3             115
4             110
5             80
6             20
  1. «Итоговая» таблица «customer_id» должна быть в единственном числе.
  2. Сумма = (СУММ (Все заказы клиентов. Деньги) - СУММ (Все заказы клиентов. Деньги))
  3. "Деньги" могут быть NULL

В таблице "Заказы" может быть customer_id, а в таблице "Сбор" может не быть

Or

В таблице «Сбор» может быть customer_id, а в таблице «Заказы» может не быть

Как я могу написать единый запрос для вывода «Итоговой» таблицы?


mysql sql jointable
person MarsPeople    schedule 14.04.2014    source источник

Ответы (5)


arrow_upward
2
arrow_downward

Самый быстрый способ - объединить ваши данные с отрицательной денежной величиной в таблице collecting:

-- load test data
    create table orders(customer_id int, money int);
    insert into orders values
      (3,120),
      (5,80),
      (3,45),
      (3,70),
      (6,20);
    create table collecting(customer_id int,money int);
    insert into collecting values
      (3,50),
      (3,70),
      (4,20),
      (4,90);

-- populate Total table
    create table Total(customer_id int,Amount int);
    insert into Total
      select oc.customer_id,sum(oc.money) Amount
      from (
           select customer_id,coalesce(money,0) money from orders
           union all
           select customer_id,coalesce(-money,0) money from collecting
           ) oc
      group by oc.customer_id;

-- return results   
    select * from Total;

SQL Fiddle: http://www.sqlfiddle.com/#!2/deebc

person Ron Smith    schedule 14.04.2014
comment
Я добавил COALESCE (money, 0) для нулевых значений, тогда я думаю, что этот запрос самый короткий и полезный. - person MarsPeople; 15.04.2014

arrow_upward
3
arrow_downward

Следующее возвращает ожидаемый результат.

SELECT 
customer_id,
SUM(amount) as amount
FROM (
  SELECT customer_id, SUM(money) as amount
  FROM orders GROUP BY customer_id

  UNION ALL

  SELECT customer_id, SUM(money) * -1 as amount
  FROM collecting GROUP BY customer_id
) as tb
GROUP BY customer_id;

customer_id = 4 возвращает -110, а не 110, так как это только в таблице сбора.

Пример: http://www.sqlfiddle.com/#!2/3b922/5/0

person Erico    schedule 14.04.2014

arrow_upward
0
arrow_downward

Вам нужно сделать это путем предварительного агрегирования данных. Если я предполагаю, что первым идет orders, вы можете использовать left outer join:

select o.customer_id, (o.money - coalesce(c.money)) as Amount
from (select o.customer_id, sum(o.money) as money
      from orders o
      group by o.customer_id
     ) o left outer join
     (select c.customer_id, sum(c.money) as money
      from collecting c
      group by c.customer_id
     ) c
     on o.customer_id = c.customer_id;
person Gordon Linoff    schedule 14.04.2014
comment
Этот запрос работает, только если клиент находится в обеих таблицах. - person Erico; 15.04.2014

arrow_upward
0
arrow_downward

Примерно так должно работать:

SELECT CASE 
        WHEN c.customer_id IS NULL
            THEN o.customer_id
        ELSE c.customer_id
        END
    ,sum(o.MONEY) - sum(c.MONEY)
FROM orders o
OUTER JOIN collecting c ON c.customer_id = o.customer_id
GROUP BY 1
person Andreas    schedule 14.04.2014

arrow_upward
0
arrow_downward

Попробуй это:

SELECT customer_id, SUM(o_money - c_money) AS Amount
FROM (
     SELECT customer_id, money AS o_money, 0 AS c_money FROM orders
     UNION ALL
     SELECT customer_id, 0 AS o_money, money AS c_money FROM collecting
     ) total
GROUP BY customer_id;

Согласно вашему описанию, вам нужен FULL [OUTER] JOIN, который представляет собой комбинацию LEFT JOIN и RIGHT JOIN. Многие базы данных не поддерживают FULL JOIN, поэтому вам нужно использовать UNION ALL для достижения того же эффекта.

person Racil Hilan    schedule 14.04.2014