mysql - sql计算m/n关系中的多个列

Question

我正在努力处理三个相关的表格，我喜欢在其中显示使用特定代码的域数量和地址数量。我有三张 table :

表位置:

ID | domain       | address             
======================================
 1 | example.com  | example.com/siteA   
 2 | example.com  | example.com/siteB   
 3 | example.com  | sub.example.com     
 4 | whatever.com | whatever.com 
 5 | foobar.com   | foobar.com/site123  
 6 | foobar.com   | foobar.com/site     

表代码:

ID | code        
==========
 1 | ABC 
 2 | DEF  

表代码位置:

code_id | location_id        
=====================
 1      | 1 
 1      | 2    
 1      | 3
 1      | 4
 2      | 5 
 2      | 6 

我喜欢得到什么(编辑:当将查询限制为地址='example.com'时):

ID | code  | domaincount | addresses      
=====================================
 1 | ABC   |  2          | 3
 2 | DEF   |  1          | 2

这意味着代码“ABC”用于一个域(example.com)和三个子域； “DEF”用于一个域和两个子域

domaincount 显示代码使用的域数量，而地址显示代码使用的给定域的地址数量。我不确定这是否可以用一条语句来实现

Answer 1

您可以使用count(distinct)来做到这一点:

select c.code, count(distinct l.domain) as numdomains,
       count(l.address) as numaddress
from codes c left outer join
     code_locations cl
     on c.id = cl.code_id left outer join
     locations l
     on l.id = cl.location_id
group by c.code;

编辑:

也许:

select c.code, count(distinct l.domain) as numdomains,
       count(l.address) as numaddress,
       sum(case when l.address like '%example.com% then 1 else 0 end) as NumAddressExample
from codes c left outer join
     code_locations cl
     on c.id = cl.code_id left outer join
     locations l
     on l.id = cl.location_id
group by c.code;