注意未定义的索引: phpmyadmin存储过程中的ORDER

Question

我编写了一个类似于以下代码的过程：

DELIMITER $$
CREATE PROCEDURE get_news(IN lat DECIMAL(10,8), IN lon DECIMAL(11,8), IN miles FLOAT)
BEGIN
    SELECT `latitude`,`longitude`,COUNT(*) count, (
        3959 *
        acos(
            cos(radians(38.9071923)) *
            cos(radians(`latitude`)) *
            cos(radians(`longitude`) - radians(77.0368707)) +
            sin(radians(38.9071923)) *
            sin(radians(`latitude`))
        )
    ) `distance`
    FROM `news`
    GROUP BY `latitude`,`longitude`
    HAVING `distance` < miles;
END$$
DELIMITER ;

当我从phpmyadmin调用这个过程时，我得到以下错误：

Notice in ./libraries/sql-parser/src/Utils/Query.php#570
 Undefined index: ORDER BY

Backtrace

./libraries/sql-parser/src/Utils/Query.php#666: SqlParser\Utils\Query::getClause(
object,
object,
string 'ORDER BY',
integer -1,
boolean false,
)
./libraries/DisplayResults.php#1385: SqlParser\Utils\Query::replaceClause(
object,
object,
string 'ORDER BY',
string '',
)
./libraries/DisplayResults.php#4368: PMA\libraries\DisplayResults->_getUnsortedSqlAndSortByKeyDropDown(
array,
string '',
)
./libraries/sql.lib.php#1643: PMA\libraries\DisplayResults->getTable(
object,
array,
array,
boolean false,
)
./libraries/sql.lib.php#1965: PMA_getHtmlForSqlQueryResultsTable(
object,
string './themes/pmahomme/img/',
NULL,
array,
boolean false,
integer 80,
integer 80,
NULL,
object,
array,
)
./libraries/sql.lib.php#2184: PMA_getQueryResponseForResultsReturned(
object,
array,
string 'news-api',
string '',
NULL,
NULL,
object,
string './themes/pmahomme/img/',
integer 80,
integer 80,
NULL,
NULL,
NULL,
NULL,
NULL,
string 'CALL get_news_count(38.9071923,-77.0368707,100)',
NULL,
)
./import.php#800: PMA_executeQueryAndGetQueryResponse(
array,
boolean false,
string 'news-api',
string '',
NULL,
string 'CALL get_news_count(38.9071923,-77.0368707,100)',
NULL,
NULL,
NULL,
NULL,
string 'db_structure.php',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string 'CALL get_news_count(38.9071923,-77.0368707,100)',
NULL,
NULL,
)

当我直接运行查询时，它没有错误，但是当我调用这个过程时，我在phpmyadmin中得到了上面的错误。如果有人遇到过这个问题，请帮助我。

谢谢

Answer 1

您使用的是没有聚合函数的HAVING。如果您真的想在这里使用HAVING COUNT(xyz) = 5子句，可以使用HAVING。否则，只需使用WHERE子句即可。

具有是聚合函数的Where子句。当您想要使用包含AVG、SUM、COUNT等聚合函数的条件时，您需要使用具有的。如果您只想检查一个条件，那么使用Where子句代替。

Answer 2

尝尝这个

SELECT `latitude`,`longitude`,COUNT(*) count, (
            3959 *
            acos(
                cos(radians(38.9071923)) *
                cos(radians(`latitude`)) *
                cos(radians(`longitude`) - radians(77.0368707)) +
                sin(radians(38.9071923)) *
                sin(radians(`latitude`))
            )
        ) `distance`
        FROM `news`
    WHERE`distance` < miles
        GROUP BY `latitude`,`longitude`;