GIS SQL查询逻辑语法

Question

我有一个GIS层，它有数千个设备ID

• 对于每个设施ID，可以有多个化学品ID
• 对于每个化学ID，最多有3种区域类型。A、B、C区。但并不总是有3种区域类型，它是不同的
• 每个设施标识的化学标识的数量可以从1到多个不等

我试图做的是分解（或分组）这个层，显示哪个设备ID区域具有最大的缓冲区距离，同时包括ZoneType（重要）

这些是田地

             FacilityID, ChemicalID, ZoneType, ZoneDistance
ex rows:        1              2           A          1000
                1              2           B          900
                1              2           C          500
                1              5           A          1200
                1              5           B          900
                1              7           B          2000
                1              7           C          900
                2              13          A          200
                2              13          B          300
                2              13          C          600

预期结果：每个FacilityID有一行，其中包含最大缓冲区和特定区域类型。所以对于设施ID 1-我想要一行，它将是分区类型B，分区间距为2000

FacilityID,ZoneType, ZoneDistance  
    1          B         2000
    2          C         600

我尝试了一些SQL语句，我得到了每个ZoneType的设备ID和最大zoneInstance。我只需要一个设备ID上所有分区类型的最大分区位置

SELECT max(ZoneDistance), ZoneType, FacilityID
FROM AllZones group by ZoneType, FacilityID; 

我也尝试过一个子查询，但也不起作用

我对SQL还不太熟悉，而且我似乎无法找到获得结果的逻辑。SQL或Python中的答案

Answer 1

Select f.FacilityID, 
     f.ZoneType,
     m.MaxZoneDistance
from AllZones f
     join (
          SELECT max(ZoneDistance) MaxZoneDistance,
               FacilityID 
          FROM AllZones 
          group by FacilityID
          ) m
          on f.facilityID=m.facilityID 
               and MaxZoneDistance=ZoneDistance

输出

FacilityID  ZoneType    MaxZoneDistance
1           B           2000
2           C           600

Answer 2

在access中使用此示例。我准备了一个MySQL的演示

Achieving ROW_NUMBER / PARTITION BY in MS Access

SQL Fiddle Demo

SELECT *
FROM (
        SELECT 
            t1.`ZoneType`,
            t1.`FacilityID`,
            t1.`ZoneDistance`,
            COUNT(*) AS `rn`
        FROM       Facility AS t1
        INNER JOIN Facility AS t2
                ON t1.`FacilityID`   = t2.`FacilityID`
               AND t1.`ZoneType`     = t2.`ZoneType`
               AND t1.`ChemicalID`   = t2.`ChemicalID`
               AND t1.`ZoneDistance` <= t2.`ZoneDistance`
        GROUP BY
            t1.`ZoneType`,
            t1.`FacilityID`,
            t1.`ZoneDistance`
        ORDER BY 
            t1.`ZoneType`,
            t1.`FacilityID`,
            t1.`ZoneDistance` DESC
       ) T
WHERE rn = 1      
ORDER BY `ZoneType`,`FacilityID`

输出

| ZoneType | FacilityID | ZoneDistance | rn |
|     |      |       |  |
|        A |          1 |         1200 |  1 |
|        A |          2 |          200 |  1 |
|        B |          1 |         2000 |  1 |
|        B |          2 |          300 |  1 |
|        C |          1 |          900 |  1 |
|        C |          2 |          600 |  1 |

在我看到你的结果后，我找到了另一种方法

Second DEMO

SELECT *
FROM (
        SELECT 
            t1.`FacilityID`,
            t1.`ChemicalID`, 
            t1.`ZoneType`, 
            t1.`ZoneDistance`,
             ( SELECT COUNT(*)
               FROM Facility as t2
               WHERE t1.`FacilityID` = t2.`FacilityID`
                 AND ( ( t1.`ZoneDistance` < t2.`ZoneDistance`)
                    OR ( t1.`ZoneDistance` = t2.`ZoneDistance` and t1.`ZoneType` > t2.`ZoneType`)
                    OR ( t1.`ZoneDistance` = t2.`ZoneDistance` and t1.`ZoneType` = t2.`ZoneType` and t1.`ChemicalID` > t2.`ChemicalID`)
                     )
             ) as rn
        FROM       Facility AS t1
        ORDER BY 
            t1.`FacilityID`,        
            t1.`ZoneDistance` DESC,
            t1.`ZoneType`, 
            t1.`ChemicalID`
    ) T
WHERE rn = 0;

输出

FacilityID  ChemicalID  ZoneType    ZoneDistance    rn
2           13          C           600             0
1           7           B           2000            0