mysql - 按多个字段进行 groupBy 时计算行数

Question

我有以下查询

Select parent.*,

( Select ?????????
     from  event e
     where  e.company_id = parent.company_id
       AND e.event_type_id in (10, 11, 12)
       AND  
        e.email in 
         (Select DISTINCT u.email
             from  users u  where  u.parent_id = parent.id )


     and e.subject_id in 
        (Select DISTINCT s.subject_id from  subjects s  where s.parent_id = parent.id ) 
    GROUP by e.email, e.subject_id ) as done

from parent_table parent

我需要放一些东西来代替?????????计算子查询行数

我尝试用另一个子查询包装它

Select count(*)  from (.........)

但在这种情况下，我的内部查询看不到父表

“where 子句”中存在未知列“parent.company_id”

子查询本身返回类似的表

----------------------------------------
|   email   |    subject    |    count  |
----------------------------------------
|   email1  |    1          |    1      |
|   email2  |    5          |    3      |
|   email3  |    20         |    22     |

因此其事件按电子邮件主题对进行计数

在顶层，我只需要这样的对的数量

UPDT:

似乎

COUNT(DISTINCT e.email, e.subject_id)反而通过电子邮件、e.subject_id 进行分组对我来说效果很好

Answer 1

( Select COUNT(DISTINCT e.email, e.subject_id)
     from  event e
     where  e.company_id = parent.company_id
       AND e.event_type_id in (10, 11, 12)
       AND  
        e.email in 
         (Select DISTINCT u.email
             from  users u  where  u.parent_id = parent.id )


     and e.subject_id in 
        (Select DISTINCT s.subject_id from  subjects s  where s.parent_id = parent.id )  ) as done

from parent_table parent