Агрегатные функции возвращают неправильные значения при объединении большего количества таблиц.

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

select *, sum(o.tota), count(o.total) 
from customer c 
natural join orders o
group by c.custId;

который отлично работает.

но если я добавлю новую таблицу в запрос:

select *, sum(o.tota), count(o.total) 
from customer c 
natural join orders o
natural join cust_addresses a
group by c.custId;

то больше не получится. агрегатные функции возвращают неверные значения, потому что у каждого клиента может быть несколько адресов, и это правильно, я также хочу отобразить все их адреса. Что я могу сделать, чтобы решить проблему агрегатной функции?

Я мог бы подумать о том, чтобы сделать что-то вроде:

select *, (select total from orders o where o.custid=c.custid), ..
from customer c 
natural join orders o
natural join cust_addresses a
group by c.custId;

Но это очень медленно.

EDIT Я попробовал следующее, но мне сообщается, что поле c.custid неизвестно:

select *
from
     customer c,               
     left join (select sum(o.tota), count(o.total) from orders o where o.custid=c.custid) as o
where ...
group by c.custId;

sql sqlite select aggregate-functions
person codymanix    schedule 26.07.2010    source источник
comment
Как вы хотите работать с клиентами, у которых нет заказов? Ваш текущий запрос опускает их.   -  person Mark Byers    schedule 26.07.2010
comment
год справа, я забыл левый для левого соединения :)   -  person codymanix    schedule 26.07.2010

Ответы (2)


arrow_upward
4
arrow_downward

Простое решение: используйте два запроса.

В противном случае вы можете выполнить агрегированный расчет в подзапросе (для всей таблицы, а не для каждой строки), а затем СОЕДИНИТЬ результат подзапроса с таблицей адресов, чтобы получить дополнительные данные. Попробуй это:

SELECT *
FROM customer T1
LEFT JOIN
(
    SELECT custId,
           SUM(total) AS sum_total,
           COUNT(total) AS count_total
    FROM orders
    -- WHERE ...
    GROUP BY custId
) T2
ON T1.custId = T2.custId
-- WHERE ...
person Mark Byers    schedule 26.07.2010
comment
Спасибо за ответ. Я попробовал это сейчас, но это не сработало для меня. кажется, я не могу получить доступ к внешним таблицам из подзапроса. Смотрите мои правки, пожалуйста. - person codymanix; 26.07.2010
comment
@codymanix: вы не можете получить доступ к псевдониму из внутреннего запроса во внешнем запросе. - person Mark Byers; 26.07.2010
comment
Нет, я хочу получить доступ к псевдониму из внешнего запроса во внутреннем запросе. Я хочу использовать клиента c, который определен во внешнем запросе в предложении where внутреннего запроса. - person codymanix; 26.07.2010
comment
@codymanix: Почему ты хочешь это сделать? Если custId — это PK для таблицы клиентов, разве это не дает тот же самый результат в конце? - person Mark Byers; 26.07.2010
comment
@codymanix: Ваш вопрос не ясен. Возможно, вы могли бы изложить свои полные требования и объяснить, почему этот ответ не соответствует этим требованиям. - person Mark Byers; 26.07.2010

arrow_upward
0
arrow_downward

Попробуйте что-то вроде:

select c.custId, max(c.custName) custName, ...
       a.addrId, max(a.addrLine1) addrLine1, ...
       sum(o.total) order_total, count(o.total) order_count
from customer c 
left join orders o on c.custId = o.custId
left join cust_addresses a on c.custId = a.custId
group by c.custId, a.addrId;

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

person Community    schedule 26.07.2010
comment
Но тогда сумма и количество не отображаются правильно, если у клиента более одного заказа или адреса. - person codymanix; 26.07.2010
comment
@codymanix, вы включили ключевое поле cust_address (которое я назвал addrId, поскольку вы не опубликовали структуру таблицы) в предложение group by вашего запроса? Если это так, сумма и количество должны быть возвращены правильно. - person ; 26.07.2010
comment
но тогда у меня есть несколько строк для одного клиента. - person codymanix; 30.07.2010
comment
@codymanix: да, если для каждого клиента указано более одного адреса, вы получите одну строку для каждого адреса для каждого клиента. Вы явно сказали в своем вопросе, что хотите отобразить все их адреса; этот запрос решает проблему агрегатной функции и отображает все их адреса. - person ; 30.07.2010