如何选择计数不同(独特的卡车司机)而不按功能分组,也许没有使用Have(不确定最后)

How select count distinct (unique truckers) without group by function and maybe without using Having (not sure about last)

提问人:Volodymyr 提问时间:8/6/2020 最后编辑:Volodymyr 更新时间:8/6/2020 访问量:33

问:

我有一个任务,但无法解决:

有卡车司机,他们必须在城市之间旅行。
我们的数据库中有 2 个表中的这些旅行数据:

  1. trucker_traffic

     tt_id (key)                
     date                
     starting_point_coordinate                
     destination_coordinate                
     traveller_id                
     event_type ('travel', 'accident')                
     parent_event_id (For 'accident' event type it's tt_id of the original travel. There might be few accidents within one travel.)             
    
  2. trucker_places

     coordinate (key)                
     country                
     city                
    

我需要 SQL 查询来提取 2020 年 6 月多次往返伦敦市的所有唯一卡车司机的数量。
在同一查询中,提取发生事故的这些旅行的数量。

我的尝试示例

SELECT
    count(distinct(tt.traveller_id)),
        
FROM trucker_traffic tt

JOIN trucker_places tp
    ON tt.starting_point_coordinate = tp.coordinate
     OR tt.destination_coordinate   = tp.coordinate
WHERE 
    tp.city = 'London'
    AND month(tt.date) = 6
    AND year(tt.date) = 2020
    
GROUP BY tt.traveller_id

HAVING count(tt.tt_id) > 1

但它是选择计数与分组的不同卡车司机,并且仅当我在数据库中有一个跟踪器时才有效

对于任务的第二部分(我选择了意外旅行次数 - 我认为使用这样的功能很好

SUM(if(count(tt_id = parent_event_id),1,0))

但我不确定

MySQL 数据库

评论

0赞 Thorsten Kettner 8/6/2020
为什么在标题中说不能使用?这使得任务变得更加困难。GROUP BY
0赞 Thorsten Kettner 8/6/2020
附带说明:您可能希望将国家限制为英格兰,因为世界上还有其他同名城市(en.wikipedia.org/wiki/London_(消歧义))。

答:

1赞 Thorsten Kettner 8/6/2020 #1

这相当复杂,因此请确保逐步执行此操作。 子句对此有所帮助。WITH

步骤

  1. 查找 2020 年 6 月往返伦敦的旅行。您可以使用 or 来查看旅行是否发生事故。INEXISTS
  2. 按旅行者对伦敦旅行进行分组,计算旅行和意外旅行,并仅保留那些旅行不止一次的旅行者。
  3. 使用此结果集来计算旅行者并总结他们的旅行。

查询

with london_travels as
(
  select 
    traveller_id,
    case when tt_id in
      (select parent_event_id from trucker_traffic where event_type = 'accident')
    then 1 else 0 end as accident
  from trucker_traffic tt
  where event_type = 'travel'
  and month(tt.date) = 6
  and year(tt.date) = 2020
  and exists
  (
    select 
    from trucker_places tp
    where tp.coordinate in (tt.starting_point_coordinate, tt.destination_coordinate)
    and tp.city = 'London'
  )
)
, london_travellers as
(
  select 
    traveller_id,
    count(*) as travels,
    sum(accident) as accident_travels
  from london_travels
  group by traveller_id
  having count(*) > 1;
)
select
  count(*) as total_travellers,
  sum(travels) as total_travels,
  sum(accident_travels) as total_accident_travels
from london_travellers;

如果你的MySQL版本不支持子句,你当然可以只嵌套查询。J.F.WITH

with a as (...), b as (... from a) select * from b;

成为

select * from (... from (...) a) b;

您在请求标题中说您不希望在查询中使用。这是可能的,但会使查询更加复杂。如果你想这样做,我把这个作为你的任务留给你。提示:您可以选择旅客,并计算每个旅客的子查询数。GROUP BY