代码之家 › 专栏 › 技术社区 › Jason Swett

是否必须嵌套此查询?

sql database mysql

Jason Swett · 技术社区 · 14 年前

我有以下问题:

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

2 回复 | 直到 14 年前

Quassnoi 14 年前

最里面的查询从为同名客户定义的所有地址中选择一个(随机)地址。(The 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) 以便更快地工作。

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

Jason McCreary 14 年前

考虑到其他两列正在使用聚合函数,我认为您不需要嵌套查询。但如果不根据你的数据进行测试,就很难确定。

试试看:

select group_concat(customer_name) customer_names,
       count(customer_name) number_of_customers,
       line_1,
       line_2,
       city,
       state_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 state_name, city, zip, line_1, line_2
 order by state_name, city, zip, line_1, line_2

在这些类型的查询中,您真的应该在列前面加上表,即。 c.customer_name