mysql - 在子查询中加入

Question

我需要在 count 子查询中使用联接。

这是我的代码，因此您可以看到我正在尝试执行的操作:

select verticals.name,
        (select count(*) from `leads` where `id_vertical` = verticals.id) as `unique_leads`,
        (select count(*) from `lead_history` where `id_vertical` = verticals.id) as `all_leads`,
        (select count(a.*) 
            from `assignments` as a
            left join `leads` as l on (l.id = a.id_lead)
            where l.id_vertical = verticals.id) as `assigned`
        from verticals

我收到语法错误，因为我猜测我的查询完全不正确。正确的写法是什么？

Answer 1

我认为这应该修复错误:

select v.name,
       (select count(*) from `leads` where `id_vertical` = v.id) as `unique_leads`,
       (select count(*) from `lead_history` where `id_vertical` = v.id) as `all_leads`,
       (select count(*) 
        from `assignments` a left join
             `leads` l 
             on (l.id = a.id_lead)
        where l.id_vertical = v.id
       ) as `assigned`
from verticals v;

这还使用表别名(用于verticals)——因此查询更容易编写和阅读。