连接多个表并根据第一个表id获取状态

问题描述：

AGENT DB TABLE

agent_id  agent_name company_name
--------  ----------  -----------
1         AAA           XXX
2         BBB           YYY
3         CCC           ZZZ
4         DDD           XYZ

DRIVER DB TABLE

agent_id  driver_id   driver_name last_viewed
--------  ----------  ----------- -----------
2         1           EEE           1
2         2           FFF           0
2         3           GGG           0
1         4           HHH           0
3         5           III           1
3         6           JJJ           1

I WANT THE OUTPUT LIKE THIS

Agent Details     Driver details

1, AAA,             1 Drivers (0 active | 1 idle)
Company name

2, BBB,             3 Drivers (1 active | 2 idle)
Company name

3, CCC,             2 Drivers (2 active | 0 idle)
Company name

I have tried this below query

$sql="SELECT a.*,d.*, COUNT(d.driver_id) AS drivers_count FROM ta_agent a JOIN ta_drivers d USING(agent_id) GROUP BY a.agent_id";

I want to show active and idle status of the driver based on last_viewed column. For example agent_id 2 have three drivers (1,2,3) and those 3 drivers have 1,0,0 in their last_viewed column. So, i want to show the output like this 1 active and 2 idle...

enter image description here

AGENT DB TABLE p>

  agent_id agent_name company_name 
--  ------ ---------- ----------- 
1 AAA XXX 
2 BBB YYY 
3 CCC ZZZ 
4 DDD XYZ 
  code>  
 
  DRIVER DB TABLE  p> 
 
 
  agent_id driver_id driver_name last_viewed 
 -------- ---------  -  ----------- ----------- 
2 1 EEE 1 
2 2 FFF 0 
2 3 GGG 0 
1 4 HHH 0 
3 5 III 1 
3  6 JJJ 1 
  code>  pre> 
 
 我想要输出 p> 
 
 
 代理详细信息驱动程序详细信息
 
1，AAA  ，1个驱动程序（0个活动| 1个空闲）
公司名称
 
2，BBB，3个驱动程序（1个活动| 2个空闲）
公司名称
 
3，CCC，2个驱动程序（2个活动| 0空闲）
公司 name 
  code>  pre> 
 
 我在下面尝试过这个问题 y  p> 
 
 
  $ sql =“SELECT a。*，d。*，COUNT（d.driver_id）AS drivers_count FROM ta_agent a JOIN ta_drivers d USING（agent_id）GROUP BY a。  agent_id“; 
  code>  pre> 
 
 我想基于 last_viewed  code>列显示驱动程序的活动和空闲状态。 例如，agent_id 2有三个驱动程序（1,2,3），这3个驱动程序在last_viewed列中有1,0,0个。 所以，我想显示输出，如1活动和2空闲......  p> 
 
 
  p> 
  div>

答

try this,.

select a.agent_id, a.agent_name, a.company_name, ifnull(cnt_all,0) total_drivers,ifnull(cnt_active,0) active_drivers, ifnull(cnt_idle,0) idle_drivers
from agent a left join (select agent_id, count(*) cnt_all
                   from driver
                   group by agent_id) cnt on a.agent_id=cnt.agent_id

left join (select agent_id, count(*) cnt_idle
                   from driver
                   where last_viewed=0
                   group by agent_id) idle on a.agent_id=idle.agent_id

left join (select agent_id, count(*) cnt_active
                   from driver
                   where last_viewed=1
                   group by agent_id) active on a.agent_id=active.agent_id

here is SQLFiddle

答

Is this what you are looking at ?

select 
concat(
  a.agent_id,' ',a.agent_name,' ',a.company_name
) as `Agent Details`,
concat(
 COUNT(d.driver_id),' Drivers (',
 ' Active ',sum(d.last_viewed = 1) 
 ,' | ',sum(d.last_viewed = 0 ),' idle ) '
)as `Driver details`

FROM AGENT a
LEFT JOIN DRIVER d USING(agent_id)
GROUP BY a.agent_id

DEMO

UODATE : from last comment

I don't want Driver Details -> 1 Drivers ( Active 0 | 1 idle ) I want Number of Drivers -> 1 Drivers, Active -> 0, Idle -> 1

select 
concat(
  a.agent_id,' ',a.agent_name,' ',a.company_name
) as `Agent Details`,

concat ( COUNT(d.driver_id),' ',' Drivers')  as `Number of Drivers`,
sum(d.last_viewed = 1) as `Active`,        
sum(d.last_viewed = 0) as `Idle`
FROM AGENT a
LEFT JOIN DRIVER d USING(agent_id)
GROUP BY a.agent_id

http://www.sqlfiddle.com/#!2/53e77/6

连接多个表并根据第一个表id获取状态

相关推荐