SQL除法仅针对最后一行给出答案

黑色阴影

我正在尝试efficiency在我的sql代码中计算，该代码需要除以2个变量vehicle_dispatched，available_cars但是对于整个列它返回相同的答案。这是我的SQL代码：

SELECT *,@vehicles_dispatched:=COUNT(DISTINCT v.vehicle_id) AS vehicles_dispatched,
@available_cars:=(SELECT COUNT(vehicle_id) FROM vehicles WHERE company_id=1) AS available_cars,
FORMAT(@vehicles_dispatched / @available_cars,2) AS efficiency
FROM driver_attendance da 
LEFT JOIN vehicles v ON v.vehicle_id=da.vehicle_id
LEFT JOIN collection co ON co.driver_attendance_id=da.driver_attendance_id
LEFT JOIN collectible cb ON cb.collectible_id = co.collectible_id
WHERE company_id=1 GROUP BY attendance_date DESC

样本输出：

vehicles_dispatched available_cars efficiency
        5                 7          0.14
        3                 7          0.14
        6                 7          0.14
        1                 7          0.14

问题是整个列都有相同的答案，而不是对每一行进行除法。

这是更新的查询，它提供了我想要的输出，这一切都要感谢@Ravinder。

SELECT *,FORMAT( vehicle_dispatched /  available_cars, 2 ) AS efficiency
 FROM(
  SELECT da.*, 
    SUM(CASE WHEN attendance_status = 4 THEN 1 ELSE 0 END) AS total_cars_collectible,
    SUM(boundary_payment)+SUM(deficit_payment) AS total_daily_collection,
    ((SUM(boundary_due)+SUM(boundary_deficit)) - (SUM(boundary_payment)+SUM(deficit_payment))) AS total_short,
    @cars_in_maintenance:=(SELECT SUM(DISTINCT(CASE WHEN vehicle_on_duty=2 THEN 1 ELSE 0 END)) AS cars_under_maintenance FROM vehicles WHERE company_id=84) AS cars_in_maintenance,
    @vehicle_dispatched:=COUNT(DISTINCT v.vehicle_id) AS vehicle_dispatched,
    @available_cars:=(SELECT COUNT(vehicle_id) FROM vehicles WHERE company_id=84) AS available_cars
    FROM driver_attendance da 
    LEFT JOIN vehicles    v  ON v.vehicle_id            = da.vehicle_id 
    LEFT JOIN collection  co ON co.driver_attendance_id = da.driver_attendance_id 
    LEFT JOIN collectible cb ON cb.collectible_id       = co.collectible_id 
   WHERE v.company_id = 84
   GROUP BY da.attendance_date DESC
 ) AS vehicles_attended

雷文德·雷迪（Ravinder Reddy）

当将诸如count，max等的聚合函数与用户定义的变量一起使用时，所产生的行为是未知的。我找不到有关的文档。

一段时间以前，我对SO提出了类似的问题：
...当使用聚合函数（如count，sum，group by）执行类似的语句时，结果模式完全不同.... SQL Fiddle上的示例...

在查询上，您可以首先计算各种值的计数，然后efficiency在外部查询中进行计算。

select vehicles_dispatched, available_cars
     , format( vehicles_dispatched /  available_cars, 2 ) as efficiency
 from(
  select da.*, count( distinct v.vehicle_id ) as vehicles_dispatched
       , count( v.vehicle_id ) as available_cars
    from driver_attendance     da 
    left join vehicles    v  on v.vehicle_id            = da.vehicle_id 
    left join collection  co on co.driver_attendance_id = da.driver_attendance_id 
    left join collectible cb on cb.collectible_id       = co.collectible_id 
   where v.company_id = 1
   group by da.attendance_date desc;
 ) as vehicles_attended

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。