mysql - 从另一个没有连接的表中选择

Question

我在下面有一个有效的查询，但我需要在我的选择语句中选择这些代理的名字和姓氏，没有任何实际值来加入表。

我的另一个表是ambition.ambition_users，它有名字、姓氏和扩展名。我让子查询正确地提取扩展并将其视为id。但是，在我的主选择中，我还想选择名字和姓氏。我似乎无法找到一种方法来使用简单的子查询来执行此操作，并且我没有任何内容可以加入表，除非我能够将我创建的id加入到ambition_users.extention上。

在不影响当前查询聚合的情况下实现提取名字和姓氏的最佳方法是什么？

这是工作查询:

select
case 
when callingpartyno       in (select extension from ambition.ambition_users)
then callingpartyno
when finallycalledpartyno in (select extension from ambition.ambition_users)
then finallycalledpartyno
 end as id

-- this is where i want to select first_name and last_name from ambition.ambition_users

, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) +                   -- outbound calls
 sum(if(legtype1 = 2, 1, 0))  as total_calls
, now() as time_of_report
, curdate() as date_of_report
 from 
  ambition.session a
  join ambition.callsummary b
  on a.notablecallid = b.notablecallid
 where 
date(a.ts) >= curdate()
 and (
callingpartyno in (select extension from ambition.ambition_users
)
 or  finallycalledpartyno in (select extension from ambition.ambition_users
)
)
 group by
 id;

Answer 1

为什么需要这么多子查询。您可以使用左联接并取出所有其他子查询。

    select
    COALESCE( callingpartyno, finallycalledpartyno) as id
    -- if extension is not equal to callingpartyno, it will return null because its a left join. If you use coalesce you can get the first non null value.

    , max(c.firstname)
    , max(c.lastname)
    -- if you use max you need not put that in group by clause

    , sum(duration) as total_talk_time_seconds
    , round(sum(duration) / 60,2) as total_talk_time_minutes
    , sum(if(legtype1 = 1,1,0)) as total_outbound
    , sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
    , sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
    , sum(if(legtype1 = 1, 1, 0)) +                   -- outbound calls
     sum(if(legtype1 = 2, 1, 0))  as total_calls
    , now() as time_of_report
    , curdate() as date_of_report
     from 
      ambition.session a
      join ambition.callsummary b
      on a.notablecallid = b.notablecallid
---- adding a left join
    left join 
     ambition.users c
    on c.extension = callingpartyno or c.extension = finallycalledpartyno
     where 
    date(a.ts) >= curdate()
     group by id;