代码之家 › 专栏 › 技术社区 › codymanix

聚合函数在连接更多表时返回错误的值

aggregate-functions select sqlite sql

codymanix · 技术社区 · 15 年前

我想显示所有的客户和他们的地址,以及他们的订单数量和总金额。我的查询如下所示:

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;

但这很慢。

我现在尝试了以下操作,但它告诉我字段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;

2 回复 | 直到 15 年前

Mark Byers 15 年前

简单的解决方案:使用两个查询。

否则,您可以在子查询中(对整个表,而不是每行)进行聚合计算,然后将子查询的结果与addresses表连接起来,以获得额外的数据。试试这个:

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 ...

user359040 user359040 15 年前

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;

假设您想要所有客户,无论他们是否有任何订单或地址。

推荐文章