<p>在access中使用此示例。我准备了一个MySQL的演示</p>
<p><a href="https://stackoverflow.com/questions/21917637/achieving-row-number-partition-by-in-ms-access">Achieving ROW_NUMBER / PARTITION BY in MS Access</a></p>
<p><strong><a href="http://sqlfiddle.com/#!9/746b653/13" rel="nofollow noreferrer">SQL Fiddle Demo</a></strong></p>
<pre><code>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`
</code></pre>
<p><strong>输出</strong></p>
<pre><code>| 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 |
</code></pre>
<p>在我看到你的结果后,我找到了另一种方法</p>
<p><strong><a href="http://rextester.com/ASII5141" rel="nofollow noreferrer">Second DEMO</a></strong></p>
<pre><code>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;
</code></pre>
<p><strong>输出</strong></p>
<pre><code>FacilityID ChemicalID ZoneType ZoneDistance rn
2 13 C 600 0
1 7 B 2000 0
</code></pre>