次平衡的使用

Question

下面是数据库和表的详细信息。数据库名称- mis_v1表1名称- trips表2名称-客户端

我尝试了以下查询

查询1

select cl.client_name CLIENT
     , count(t.trip_type) TRIPS
     , count(distinct t.vehicle_reg_no) VEHICLES
  from mis_v1.trips t
  JOIN mis_v1.client cl 
    ON cl.id = t.client_id 
 group 
    by cl.client_name;

查询1结果

CLIENT      TRIPS   VEHICLES
anz-ABlr    118     16  
citrix-CBlr 159     15  
dxc-DBlr    26      5   
Eps-Blr     116     24  
goc-GocHyd  191     10
Unisys-BLR  192     55
Wipro-Ncr   86      33
Wipro-Pnq   10      5

查询2

select cl.client_name CLIENT
     , count(t.trip_delay_reason) LATE_TRIPS
  FROM mis_v1.trips t
  JOIN mis_v1.client cl 
    ON cl.id = t.client_id
 where t.trip_delay_reason = "DRIVER"
 group 
    by cl.client_name;

查询2结果

CLIENT       LATE_TRIPS
anz-ABlr        53
citrix-CBlr     25
dxc-DBlr        1
Wipro-Ncr       1
goc-GocHyd      17

我需要下面的结果

CLIENT     TRIPS    VEHICLES    LATE_TRIPS
anz-ABlr    118     16          53
citrix-CBlr 159     15          25
dxc-DBlr    26      5           1
Eps-Blr     116     24          -
goc-GocHyd  191     10          17
Unisys-BLR  192     55          -
Wipro-Ncr   86      33          1
Wipro-Pnq   10      5           -

请提前给我solution.Thanks

Answer 1

如果我对你的理解正确，你需要这样的东西：

select cl.client_name CLIENT
     , count(t.trip_type) TRIPS
     , count(distinct t.vehicle_reg_no) VEHICLES
     , coalesce(cast(count(case when t.trip_delay_reason = "DRIVER" then 1 else null end) as char), '-') LATE_TRIPS
from mis_v1.trips t
JOIN mis_v1.client cl 
  ON cl.id = t.client_id 
group 
  by cl.client_name;

因此，请阅读更多关于COUNT aggreagate函数和可能的CASE运算符的内容。