sql - 正确的嵌套查询

Question

当我运行以下 T-SQL 时

use xxx

select      
    t.Vehicle,
    t.Distance,
    t.FuelConsumption,
    d.LastConnection,
    v.Make,
    v.Model
from
    dbo.Trips t
left join   
    dbo.Vehicles v on v.Id = t.Vehicle
left join   
    dbo.Devices d on d.Id = v.DeviceId
where       
    t.Date > '2020-03-02' and Distance > 1
order by    
    t.Vehicle, t.FuelConsumption 

我得到 34 行，如下所示:



第一辆车 ID 76 已经完成了 2 次旅行，1 次记录了燃料，另一个没有。这就是我试图建立的。

所以我尝试了以下嵌套查询

 select     
     t.Vehicle,
     d.LastConnection,
     v.Make,
     v.Model,
     count(t.id) as TripCount,
     sum(NoFuelRecord) as NoFuelRecord,
     sum(FuelRecorded) as FuelRecorded          
from
    (select count(Id) as NoFuelRecord
     from dbo.Trips 
     where Distance > 1 and FuelConsumption <= 0 and Date > '2020-03-02'
     group by Vehicle)  as NoFuelRecord,
    (select count(Id) as FuelRecorded
     from dbo.Trips 
     where Distance > 1 and FuelConsumption > 0 and Date > '2020-03-02'
     group by Vehicle) as FuelRecorded,
    dbo.Trips t         
left join   
    dbo.Vehicles v on v.Id = t.Vehicle
left join   
    dbo.Devices d on d.Id = v.DeviceId
where
    t.Date > '2020-03-02' and Distance > 1
group by
    t.Vehicle, v.Make, v.Model, d.LastConnection
order by    
    t.Vehicle

返回以下结果:



所以我希望在第 1 行看到的是 TripCount: 2, NoFuelRecord: 1, FuelRecorded: 1

我什至不接近!请问我该怎么做？

Answer 1

很难跟踪为什么您的查询没有返回预期的输出。但是根据您的初始查询和您描述的预期结果，这应该为您提供您想要的

WITH CTE as (
SELECT      t.Vehicle,
            t.Distance,
            t.FuelConsumption,
            t.Id,
            d.LastConnection,
            v.Make,
            v.Model
FROM        dbo.Trips t
LEFT JOIN   dbo.Vehicles v on v.Id = t.Vehicle
LEFT JOIN   dbo.Devices d on d.Id = v.DeviceId
WHERE       t.Date > '2020-03-02' and Distance > 1
)
SELECT Vehicle,
            LastConnection,
            Make,
            Model,
            COUNT(Id) AS TripCount,
            SUM(CASE WHEN FuelConsumption > 0 THEN 1 ELSE 0 END) AS FuelRecorded,
            SUM(CASE WHEN FuelConsumption <= 0 THEN 1 ELSE 0 END) AS NoFuelRecorded 
FROM CTE 
GROUP BY  Vehicle,
            LastConnection,
            Make,
            Model