MySQL如何根据WHERE子句中的参数顺序获取结果

Question

我有以下查询语句：

SELECT idelm_na AS ID, nd.lat_nd AS lat, nd.lon_nd AS 'lon' , adr.road_adr AS 'road' 
FROM node_nd nd, node_address_na na, address_adr adr
WHERE 
    ((ROUND(lat_nd,6)=ROUND(14.654733,6) AND ROUND(lon_nd,6)=ROUND(121.058403,6)) OR 
    (ROUND(lat_nd,6)=ROUND(14.654791,6) AND ROUND(lon_nd,6)=ROUND(121.062386,6)) OR
    (ROUND(lat_nd,6)=ROUND(14.654791,6) AND ROUND(lon_nd,6)=ROUND(121.064343,6)) OR
    (ROUND(lat_nd,6)=ROUND(14.654754,6) AND ROUND(lon_nd,6)=ROUND(121.064403,6)) OR

    (ROUND(lat_nd,6)=ROUND(14.654648,6) AND ROUND(lon_nd,6)=ROUND(121.06445,6)) OR 
    (ROUND(lat_nd,6)=ROUND(14.653869,6) AND ROUND(lon_nd,6)=ROUND(121.064798,6)) OR
    (ROUND(lat_nd,6)=ROUND(14.653865,6) AND ROUND(lon_nd,6)=ROUND(121.065399,6)) OR
    (ROUND(lat_nd,6)=ROUND(14.653880,6) AND ROUND(lon_nd,6)=ROUND(121.066532,6)))
AND na.idelm_na = nd.idelm_nd AND adr.id_adr = na.idadr_na;

它的作用是返回与坐标（纬度，经度）关联的adr.road_adr。默认情况下，它返回的内容按 idelm_na 排序。有没有办法重写此代码，根据 OR 参数/参数的顺序返回结果？我可以将 UNION ALL 与单独的 SELECT 语句一起使用，但我认为这太慢了。我希望有人能在这里帮助我。谢谢！

Answer 1

首先，不要对连接使用逗号分隔语法。使用 ISO 标准。其次，为了实现您想要的目标，您需要以不同的方式构建查询。如果您为输入提供排序顺序，您就可以实现您想要的效果。除了提供排序功能之外，您还可以更轻松地构建输入列表：

Select idelm_na AS ID, nd.lat_nd AS lat, nd.lon_nd AS 'lon' , adr.road_adr AS 'road' 
From node_nd As ND
    Join node_address As NA
        On NA.idelm_nd = ND.idelm_na
    Join address_adr As ADR
        On ADR.id_adr = NA.idadr_na
    Join    (
            Select 1 As Rnk, 14.654733 As Lat, 121.058403 As Lon
            Union All Select 2, 14.654791, 121.062386
            Union All Select 3, 14.654791, 121.064343
            Union All Select 4, 14.654754, 121.064403
            Union All Select 5, 14.654648, 121.064450
            Union All Select 6, 14.653869, 121.064798
            Union All Select 7, 14.653865, 121.065399
            Union All Select 8, 14.653880, 121.066532
            ) As Z
        On Round(lat_nd, 6) = Z.Lat
            And Round(lon_nd, 6) = Z.Lon
Order By Z.Rnk

Answer 2

您可以在 SELECT 语句中添加“ORDER BY”子句：

SELECT idelm_na AS ID, ... WHERE ... ORDER BY lat_nd, lon_nd;

这将首先按 lat_nd 对结果进行排序，然后按 lon_nd 对结果进行排序。