mysql - 这个查询必须嵌套吗？

Question

我有以下查询:

select group_concat(customer_name) customer_names,
       count(customer_name) number_of_customers,
       line_1,
       line_2,
       city,
       state_name,
       zip
  from (select c.name customer_name,
               ad.*,
               s.name state_name
          from address ad
          join account_address aa on aa.address_id = ad.id
          join account a on aa.account_id = a.id
          join customer c on a.customer_id = c.id
          join state s on ad.state_id = s.id
 group by c.name) a
 group by state_name, city, line_1, line_2
 order by state_name, city, line_1, line_2

我是否必须按照我所做的方式嵌套它以获得两层分组？如果不需要，我宁愿不使用任何子查询。

编辑:这是我最终使用的查询:

select group_concat(distinct c.name) customer_names,
       count(distinct c.name) number_of_customers,
       line_1,
       line_2,
       city,
       s.name,
       zip
  from address ad
  join account_address aa on aa.address_id = ad.id
  join account a on aa.account_id = a.id
  join customer c on a.customer_id = c.id
  join state s on ad.state_id = s.id
group by s.name, city, zip, line_1, line_2
order by s.name, city, zip, line_1, line_2

Answer 1

最里面的查询从所有为同名客户定义的地址中选择一个(随机)地址。 (顺便说一下，state 甚至不必属于地址)

查询似乎是按州、城市和街道地址统计客户。如果客户有多个地址怎么办？应该计算一次还是两次(如果计算一次，在哪个地址计算)？

换句话说，查询应该为这样的地址返回什么:

Customer1  NY  NYC   Broadway
Customer1  CA  LA    Sunset Boulevard
Customer2  CA  LA    Sunset Boulevard

您总共有三两个客户吗？如果有两个，他们都住在 LA 吗？

更新:

如果你想在每个地址上重复，那么你根本不需要最里面的GROUP BY:

SELECT  GROUP_CONCAT (DISTINCT customer_name) customer_names,
        COUNT(DISTINCT customer_name) number_of_customers,
        line_1,
        line_2,
        city,
        state_name,
        zip
FROM    address ad
JOIN    state s
ON      s.id = ad.state_id
JOIN    account_address aa
ON      aa.address_id = ad.id
JOIN    account a
ON      a.id = aa.account_id
JOIN    customer c
ON      c.id = a.customer_id
GROUP BY
        ad.state_id, ad.city, ad.line_1, ad.line_2

在 address (state_id, city, line_1, line_2) 上创建一个索引，以便更快地工作。

请注意，如果同一地址可能有不同的邮政编码，则不会定义返回哪个邮政编码。