代码之家 › 专栏 › 技术社区 › red-X

在sql中获取给定半径内的所有标记

distance google-maps-api-3 coldfusion sql

1

red-X · 技术社区 · 16 年前

我试图用一个sql语句获取数据库中存储的所有位置 here

我的sql语句是:

SELECT TOP(10)
                *,
                (
                    6371 *
                    acos(
                        cos(
                            radians(<cfqueryparam cfsqltype="CF_SQL_Numeric" value="#FORM.latitude#">)
                        ) *
                        cos(
                            radians( custLat )
                        ) *
                        cos(
                            radians( custLong ) -
                            radians(<cfqueryparam cfsqltype="CF_SQL_Numeric" value="#FORM.longtitude#">)
                        ) +
                        sin(
                            radians(<cfqueryparam cfsqltype="CF_SQL_Numeric" value="#FORM.latitude#">)
                        ) *
                        sin(
                            radians( custLat )
                        )
                    )
                ) AS distance
                FROM customers
                HAVING distance < 25
                ORDER BY distance;

Invalid column name 'distance'.

在这条线上: radians(<cfqueryparam cfsqltype="CF_SQL_Numeric" value="#FORM.latitude#">)

为什么会出现此错误,如何修复?

2 回复 | 直到 6 年前

1

2

PPShein 16 年前

你做错了什么。以下是解决方案:

SELECT distance FROM

(SELECT TOP(10)

                *,
                (
                    6371 *
                    acos(
                        cos(
                            radians(<cfqueryparam cfsqltype="CF_SQL_Numeric" value="#FORM.latitude#">)
                        ) *
                        cos(
                            radians( custLat )
                        ) *
                        cos(
                            radians( custLong ) -
                            radians(<cfqueryparam cfsqltype="CF_SQL_Numeric value="#FORM.longtitude#">)
                        ) +
                        sin(
                            radians(<cfqueryparam cfsqltype="CF_SQL_Numeric"  value="#FORM.latitude#">)
                        ) *
                        sin(
                            radians( custLat )
                        )
                    )
                ) AS distance
                FROM customers
                ) newTable
                HAVING distance < 25
                GROUP BY distance
                ORDER BY distance;

2

0

mahesh 16 年前