mysql - SQL部门只给出最后一行的答案

Question

我正在尝试在我的 sql 代码中计算 efficiency，这需要除以 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

Answer 1

当 count、max 等聚合函数与用户定义的变量一起使用时，结果行为未知。我找不到相关文档。

一段时间后我在 SO 上提出了一个类似的问题:
... When a similar statement was executed with aggregate functions like count, sum, group by, the results pattern was entirely different .... Example on SQL Fiddle ...

在您的查询中，您可以先计算各种值的计数，然后在外部查询中计算效率。

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