如何在SQL中通过表间计算获取带充电口的免费停车位数量

问题需求 #

需要计算带充电口的免费停车位数量，公式为：充电口总数（ElectricOutlet） - 已占用充电车位（OccupiedElectricSlots）

现有查询 #

你之前编写的两个独立SQL查询：

-- 查询1：统计已占用充电车位
SELECT 
COUNT(ca.Plate) as 'OccupiedElectricSlots'
FROM cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on  PS.Id = Ca.ParkingSlotsId
WHERE ps.ElectricOutlet = 1
GROUP BY hs.HouseName, C.CityName

-- 查询2：统计停车场总车位、总免费车位、充电口总数
SELECT 
 MAX(Ps.SlotNumber) as 'ParkingSlotTotal'
,MAX(PS.SlotNumber) - Count(ca.Plate) as 'FreeSlots'
,SUM(CAST(PS.ElectricOutlet AS INT)) as 'ElectricOutlet'
,Hs.HouseName
,C.CityName
FROM Cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on  PS.Id = Ca.ParkingSlotsId
GROUP BY hs.HouseName, C.CityName

优化后的解决方案 #

方案1：用CTE关联两个查询结果

先通过CTE计算出每个停车场的已占用充电车位，再和主查询关联得到目标值：

WITH OccupiedElectric AS (
    SELECT 
        hs.HouseName,
        C.CityName,
        COUNT(ca.Plate) as OccupiedElectricSlots
    FROM cities C
    JOIN ParkingHouses HS on C.Id = hs.CityId
    JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
    LEFT JOIN Cars Ca on PS.Id = Ca.ParkingSlotsId
    WHERE ps.ElectricOutlet = 1
    GROUP BY hs.HouseName, C.CityName
)
SELECT 
    MAX(Ps.SlotNumber) as ParkingSlotTotal,
    MAX(PS.SlotNumber) - Count(ca.Plate) as FreeSlots,
    SUM(CAST(PS.ElectricOutlet AS INT)) as ElectricOutlet,
    oe.OccupiedElectricSlots,
    SUM(CAST(PS.ElectricOutlet AS INT)) - oe.OccupiedElectricSlots as FreeElectricSlots,
    Hs.HouseName,
    C.CityName
FROM Cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on PS.Id = Ca.ParkingSlotsId
JOIN OccupiedElectric oe ON hs.HouseName = oe.HouseName AND C.CityName = oe.CityName
GROUP BY hs.HouseName, C.CityName, oe.OccupiedElectricSlots

方案2：单查询直接计算（更高效）

无需拆分查询，用CASE语句在主查询里直接统计已占用充电车位，一步得到所有结果：

SELECT 
    MAX(Ps.SlotNumber) as ParkingSlotTotal,
    MAX(PS.SlotNumber) - COUNT(ca.Plate) as FreeSlots,
    SUM(CAST(PS.ElectricOutlet AS INT)) as ElectricOutlet,
    -- 仅统计有车停放的充电车位
    COUNT(CASE WHEN ps.ElectricOutlet = 1 AND ca.Plate IS NOT NULL THEN 1 END) as OccupiedElectricSlots,
    -- 直接计算带充电口的免费车位
    SUM(CAST(PS.ElectricOutlet AS INT)) - COUNT(CASE WHEN ps.ElectricOutlet = 1 AND ca.Plate IS NOT NULL THEN 1 END) as FreeElectricSlots,
    Hs.HouseName,
    C.CityName
FROM Cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on PS.Id = Ca.ParkingSlotsId
GROUP BY hs.HouseName, C.CityName

关键说明 #

• 方案2性能更优，只需要遍历一次数据表
• CASE语句的作用是精准筛选出带充电口且有车辆停放的车位，得到OccupiedElectricSlots
• 最终字段FreeElectricSlots就是你需要的带充电口的免费停车位数量

内容的提问来源于stack exchange，提问作者Daniel