我怎样才能“为每个”做到这一点？ SQL Server 2008 中的代码不使用游标？

Question

如果可能的话，我希望在不使用 CURSOR 的情况下在 SQL 中执行以下伪代码。

for each zipcode
{
    -- What city is this zipcode in? A zipcode can be in multiple cities, 
    -- but one city area is always way greater that the others.
    -- Eg. 90210 is 96% in the city of Beverly Hills. 
    -- The remaining 4% is that it s in some other fringe cties .. 
    --but only just (some mismatch mapping errors).

    ** grab the city largest shape area which this zip is a part of **
}

现在，我有一些 SQL 可以帮助我们。

-- All zipcodes and each boundary/shapefile.
SELECT ZipCodeId, Boundary FROM ZipCodes -- Boundary is a GEOGRAPHY field type.

To determine if a zipcode boundary is in a city....
SELECT CityId, CityName, 
    City.Boundary.Intersection(@someZipCodeBoundary).STArea() AS Area
FROM Cities
WHERE City.Boundary.Intersects(@someZipCodeBoundary) = 1

并获取交集面积（因为我们想要最高的交集面积，即 TOP(1) ORDER BY Area DESC 或 DISTINCT 与 ORDER BY 之类的东西我们使用 Intersection SQL 方法

注意：Intersects 和 Intersection 是两种不同的 Sql 方法。

Answer 1

尝试另一种方法。获取某个地方的人口普查统计数据，并筛选出按相同邮政编码分组的人口最多的城市。

例如，像这个地名词典。

Answer 2

好的 - 明白了；）诀窍是使用 PARTITION BY。当我意识到我以前做过类似的事情时，@In Sane 给了我这个想法:)

所以..我们开始..

SELECT 
    a.CityId, a.Name, b.ZipCodeId, b.Code, a.Boundary.STIntersection(b.Boundary).STArea() AS Area, 
    RANK() OVER (PARTITION BY b.ZipCodeId ORDER BY a.Boundary.STIntersection(b.Boundary).STArea() DESC) AS Area_Rank
FROM 
    Cities a
    INNER JOIN ZipCodes b on a.Boundary.STIntersects(b.Boundary) = 1
    where b.Code = 12010 OR b.Code = 90210


CityId      Name                                                                                                 ZipCodeId   Code                 Area                   Area_Rank
----------- ---------------------------------------------------------------------------------------------------- ----------- -------------------- ---------------------- --------------------
2166        Los Angeles                                                                                          9331        90210                13235413.8430175       1
1686        Beverly Hills                                                                                        9331        90210                10413397.1372613       2
2635        West Hollywood                                                                                       9331        90210                0                      3
14570       Amsterdam                                                                                            29779       12010                15369521.9602067       1
14921       Hagaman                                                                                              29779       12010                1394562.70390667       2
14856       Fort Johnson                                                                                         29779       12010                211058.884834718       3
14651       Broadalbin                                                                                           29779       12010                0                      4

(7 row(s) affected)

所以在这个过滤的例子中（按邮政编码12010或90210过滤），我们可以看到这个邮政编码存在于4个不同的城市/城镇。每个邮政编码可以有一对多结果，然后按区域值排序..但这里的关键是 PARTITION 关键字..它按邮政编码组或分区进行排序。非常时髦:)注意到邮政编码 90210 是如何拥有自己的排名结果的吗？和12010一样吗？

接下来，我们将其设为子查询，然后获取所有 Rank == 1 :)

SELECT CityId, Name, ZipCodeId, Code, Area_Rank
FROM (
SELECT 
    a.CityId, a.Name, b.ZipCodeId, b.Code, a.Boundary.STIntersection(b.Boundary).STArea() AS Area, 
    RANK() OVER (PARTITION BY b.ZipCodeId ORDER BY a.Boundary.STIntersection(b.Boundary).STArea() DESC) AS Area_Rank
FROM 
    Cities a
    INNER JOIN ZipCodes b on a.Boundary.STIntersects(b.Boundary) = 1
    where b.Code = 12010 OR b.Code = 90210
) subQuery
WHERE Area_Rank = 1


CityId      Name                                                                                                 ZipCodeId   Code                 Area_Rank
----------- ---------------------------------------------------------------------------------------------------- ----------- -------------------- --------------------
14570       Amsterdam                                                                                            29779       12010                1
2166        Los Angeles                                                                                          9331        90210                1

(2 row(s) affected)

Sweet as candy :)

旁注：这也表明我的洛杉矶城市形状文件/边界已损坏，因为它与邮政编码 90210 相交太多（我直观地确认了这一点：P）

Answer 3

我必须说，我不知道交集/交集是什么或它们的作用，我必须澄清我不理解下面的语法 Boundary.Intersection(@someZipCodeBoundary)。 要么

假设你的这个查询返回与指定的 zipcodeBoundary 相交的所有城市，并且 Area 是它们的相交区域，

SELECT 
    CityId, 
    CityName, 
    City.Boundary.Intersection(@someZipCodeBoundary).STArea() AS Area 
FROM 
    Cities 
WHERE 
    City.Boundary.Intersects(@someZipCodeBoundary) = 1 

根据我的理解，drachenstern 上面建议的是使用 MAX 是

SELECT 
    CityId, 
    MAX(City.Boundary.Intersection(@someZipCodeBoundary).STArea()) AS Area
FROM 
    Cities 
WHERE 
    City.Boundary.Intersects(@someZipCodeBoundary) = 1 
GROUP BY CityId

这应该给你具有 MAXIMUM 的 CITYID交叉区域。

Answer 4

Max 可以吗？