sql - 返回一个不同的区域列表 SQL 查询
问题描述
我试图弄清楚如何根据我的 SQL 查询返回区域列表。
我的表由我需要访问的以下列组成,区域、经度、纬度。
Region位于 CafeAddress 表中,cafe 的经纬度位于Cafe表下。
我当前的 SQL 查询:
Select Distinct r.Region, s.Longitude, s.Latitude
From CafeAddress r , Cafe s
Order By Region
它目前返回一个完整的地区列表,因为咖啡馆在一个地区以不同的纬度和经度绘制,因此为什么咖啡馆在我的结果中重复出现。
Region Longitude Latitude
New York -73.9904097 40.7036292
New York 174.764767 -36.850759
New York 174.764795 -36.852069
New York 174.765726 -36.846546
New York 174.765746 -36.849561
San Fran 174.6295402 -36.8566343
San Fran 174.777871 -36.870234
San Fran 174.778553 -36.852031
Tokyo 174.745957 -36.854023
Mumbai 174.74697 -36.8891
我认为做一个 distinct 会限制它,但因为我要返回不再有帮助的 lat 和 long。
我想要的预期结果是这样的:
Region Longitude Latitude
New York -73.9904097 40.7036292
San Fran 174.764767 -36.850759
Mumbai 174.764795 -36.852069
只是列出了所有地区,但想不出一种方法将其包含在这些地区中。
如果我的 SQL 查询刚刚
Select Distinct r.Region
From CafeAddress r
Order By Region
它会回来
New York
San Fran
Mumbai
解决方案
WITH region_list
AS (SELECT r.Region,
s.Longitude,
s.Latitude,
ROW_NUMBER() OVER(PARTITION BY r.Region
ORDER BY s.Longitude DESC) AS row_no
FROM CafeAddress r,
Cafe s)
SELECT Region,
Longitude,
Latitude
FROM region_list
WHERE row_no = 1;
如果您没有偏好使用哪个经度和纬度,则将窗口函数与公用表表达式结合使用可能是一个不错的选择。在上面的代码中,我使用带有 Row_Number() 的 CTE 以经度从最大到最小的顺序对所有区域进行编号,如下所示:
Region Longitude Latitude row_no
New York -73.9904097 40.7036292 6
New York 174.764767 -36.850759 4
New York 174.764795 -36.852069 5
New York 174.765726 -36.846546 2
New York 174.765746 -36.849561 1
San Fran 174.6295402 -36.8566343 3
San Fran 174.777871 -36.870234 2
San Fran 174.778553 -36.852031 1
Tokyo 174.745957 -36.854023 1
Mumbai 174.74697 -36.8891 1
然后我拿我刚刚建立的那个表并通过“where row_no = 1”选择第一个实例。这给了我以下输出:
Region Longitude Latitude row_no
New York 174.765746 -36.849561 1
San Fran 174.778553 -36.852031 1
Tokyo 174.745957 -36.854023 1
Mumbai 174.74697 -36.8891 1