sql - 即使分组依据中不存在，如何检索数据？

Question

我有一个名为 mov_areas 的表，我有 SQL，正如你在表底部看到的那样，它是一个 parking 系统，我有一个这样的选择

SELECT 
    mv_ar.name localArea, 
    COUNT(*) total, 
    SUM(CASE WHEN me.mov_vehicle_id = 1 THEN 1 ELSE 0 END) CarCount, 
    SUM(CASE WHEN me.mov_vehicle_id = 2 THEN 1 ELSE 0 END) MotoCount, 
    SUM(CASE WHEN me.mov_vehicle_id = 3 THEN 1 ELSE 0 END) VanCount, 
    SUM(CASE WHEN me.mov_vehicle_id = 4 THEN 1 ELSE 0 END) OniCount, 
    SUM(CASE WHEN me.mov_vehicle_id = 5 THEN 1 ELSE 0 END) CamCount
FROM 
    mov_entrys me
INNER JOIN 
    (SELECT 
         mov_entry_ean13, MAX(created_at) as MaxDate
     FROM 
         mov_entrys
     GROUP BY 
         mov_entry_ean13) entryTemp on me.mov_entry_ean13 = entryTemp.mov_entry_ean13 and me.created_at = entryTemp.MaxDate
JOIN 
    mov_areas mv_ar on mv_ar.mov_area_id = me.mov_area_id
JOIN 
    mov_locations mov_loc on mv_ar.mov_location_id = mov_loc.mov_location_id
WHERE 
    me.validated <> 'I'
    AND me.created_at >= :dataIni
    AND me.created_at <= :dataEnd
    AND mv_ar.mov_location_id = :locationId
GROUP BY 
    mv_ar.name

结果是这样的

areaid areaname total carcount motocount vancount onicount camcount
7      AB       660   600      20        20       20       0

它工作正常，问题是 SQL 只返回有数据的区域我也想要没有数据的区域，如何使它与这个 SQL 一起工作？像这样

areaid areaname total carcount motocount vancount onicount camcount
    7      AB   660   600      20        20       20       0
    8      C    0     0        0         0        0        0
    9      D    0     0        0         0        0        0

Answer 1

首先，使用窗口函数获取最近的日期。然后，对 SELECT 中的列使用 FILTER:

SELECT mv_ar.name as localArea, 
       COUNT(*) FILTER (WHERE me.validated <> 'I' AND me.created_at >= :dataIni AND   me.created_at <= :dataEnd AND mv_ar.mov_location_id = :locationId) as total, 
       COUNT(*) FILTER (WHERE me.mov_vehicle_id = 1 AND me.validated <> 'I' AND me.created_at >= :dataIni AND me.created_at <= :dataEnd AND mv_ar.mov_location_id = :locationId) as CarCount, 
       COUNT(*) FILTER (WHERE me.mov_vehicle_id = 2 AND me.validated <> 'I' AND me.created_at >= :dataIni AND me.created_at <= :dataEnd AND mv_ar.mov_location_id = :locationId) as MotoCount, 
       COUNT(*) FILTER (WHERE me.mov_vehicle_id = 3 AND me.validated <> 'I' AND me.created_at >= :dataIni AND   me.created_at <= :dataEnd AND mv_ar.mov_location_id = :locationId) as VanCount, 
       COUNT(*) FILTER (WHERE me.mov_vehicle_id = 4 AND me.validated <> 'I' AND me.created_at >= :dataIni AND me.created_at <= :dataEnd AND mv_ar.mov_location_id = :locationId) as OniCount, 
       COUNT(*) FILTER (WHERE me.mov_vehicle_id = 5 AND me.validated <> 'I' AND me.created_at >= :dataIni AND me.created_at <= :dataEnd AND mv_ar.mov_location_id = :locationId) as CamCount, 
FROM (SELECT me.*,
             MAX(created_at) OVER (PARTITION BY mov_entry_ean13) as MaxDate
      FROM mov_entrys me
     ) me JOIN
     mov_areas mv_ar 
     ON mv_ar.mov_area_id = me.mov_area_id JOIN
     mov_locations mov_loc 
     ON mv_ar.mov_location_id = mov_loc.mov_location_id
WHERE me.created_at = me.MaxDate
GROUP BY mv_ar.name;

我可能倾向于将过滤条件放在子查询的列中。但是，根据导致行丢失的条件，方法可能会有所不同。