按城市划分的订单数量

发布于 2024-11-16 09:36:08 字数 476 浏览 0 评论 0原文

我的查询：

select  distinct a.city, 
        (
            select count(*) 
            from sales_flat_order 
            where a.email=b.customer_email 
            group by a.city
        ) as 'lora' 
from sales_flat_order_address a, sales_flat_order b 
group by a.city

结果：

City1 : 15
City2: (NULL)

预期结果：

City1 : 13
City2: 2

原文

My Query:

select  distinct a.city, 
        (
            select count(*) 
            from sales_flat_order 
            where a.email=b.customer_email 
            group by a.city
        ) as 'lora' 
from sales_flat_order_address a, sales_flat_order b 
group by a.city

Result:

City1 : 15
City2: (NULL)

Expected Result:

City1 : 13
City2: 2

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不再见 2024-11-23 09:36:08

select a.city, count(*) as 'lora' 
from sales_flat_order_address a
        INNER JOIN sales_flat_order b 
            ON a.email=b.customer_email 
group by a.city

select a.city, count(*) as 'lora' 
from sales_flat_order_address a
        INNER JOIN sales_flat_order b 
            ON a.email=b.customer_email 
group by a.city

回复收藏 0 原文

赠佳期 2024-11-23 09:36:08

SELECT count(sfoa.city) as count_of_city, sfoa.city 
FROM sales_flat_order as sfo 
LEFT JOIN sales_flat_order_address as sfoa ON sfo.entity_id=sfoa.parent_id 
WHERE sfoa.address_type="shipping" 
GROUP BY sfoa.city;

这就是您正在寻找的东西（专为 magento 1.5 设计）吗？

编辑
需要有一个地址类型过滤器，因为每个订单都会有一个帐单和送货地址。

SELECT count(sfoa.city) as count_of_city, sfoa.city 
FROM sales_flat_order as sfo 
LEFT JOIN sales_flat_order_address as sfoa ON sfo.entity_id=sfoa.parent_id 
WHERE sfoa.address_type="shipping" 
GROUP BY sfoa.city;

Is that what you are looking for (designed for magento 1.5)?

EDIT
Needed to have a filter for address type as every order will have a billing and shipping address.

回复收藏 0 原文

九歌凝 2024-11-23 09:36:08

select count(*) from sales_flat_order where a.email=b.customer_email group by a.city

可以返回多行。它不能在 SELECT 子句中用作查询字段，除非 EMAIL 字段具有某些唯一索引，例如此外，从 ANSI SQL 的角度来看，它包含语法错误。 GROUP BY 子句在这里是多余的

select count(*) from sales_flat_order where a.email=b.customer_email group by a.city

can return multiple rows. it can not be used in a SELECT clause as query field, unless EMAIL field has some unique index e.g. besides, it contains syntax error from a point of view of ANSI SQL. GROUP BY clause is redundand here

回复收藏 0 原文