mysql - SQL 连接查询选择

Question

我有以下表格:

表:产品

--------------------------       
id_product |  name_product                               
--------------------------      
1          | p1                            
2          | p2                
3          | p3  

表格:编号

-----------------------
 imei      | id_product  
-----------------------
 56789     | 1   
 56799     | 2
 56713     | 3

表:操作

----------------------
imei     |  date
------------------
56789    |31-07-2017

最后我想得到这个结果:

---------------------------------------
 name_product | number_operation  
---------------------------------------
 p1           | 1   
 p2           | 0
 p3           | 0

谢谢你

Answer 1

这将按产品计算操作次数:

select name_product, sum(num_op)
from product p1
left join number n2
  on n2.id_product = p1.id_product
left join
    (
    select imei, count(operation) as num_op
    from Operation
    group by imei
    ) x
  on x.imei = n2.imei
group by name_product

对于有过操作的产品数量:

select name_product, count(distinct o3.imei)
from product p1
left join number n2
  on n2.id_product = p1.id_product
left join Operation o3
  on o3.imei = n2.imei
group by name_product