即使查询返回结果，mysql计数也不起作用

6qqygrtg 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(319)

我正在检索此select语句的计数。

SELECT
    count(video_id),
    3959 * acos( cos( radians(51.482600) ) * cos( radians( videos.lat ) ) * cos( radians(videos.lon) - radians(-0.063200)) + sin(radians(51.482600)) * sin( radians(videos.lat))) AS distance
FROM
    videos
WHERE
    rooms >= 1 AND price <= 3500
HAVING
    distance <= 3

它一直显示行计数的空结果，但是当我删除 count(video_id) 使用*返回结果：

SELECT
    *,
    3959 * acos( cos( radians(51.482600) ) * cos( radians( videos.lat ) ) * cos( radians(videos.lon) - radians(-0.063200)) + sin(radians(51.482600)) * sin( radians(videos.lat))) AS distance
FROM
    videos
WHERE
    rooms >= 1 AND price <= 3500
HAVING
    distance <= 3

如果对语句有混淆，它会将给定位置的lat和lng与表“videos”中列的lat和lng进行比较（如果距离小于或等于3英里），并返回结果。
谢谢

mysql

来源：https://stackoverflow.com/questions/52731393/mysql-count-not-working-even-doe-the-query-is-returning-results

2条答案

按热度按时间

ct2axkht1#

您可以使用子查询：

SELECT video_id, count(video_id) as num, distance FROM (SELECT video_id ,3959 * acos( cos( radians(51.482600) ) * cos( radians( videos.lat ) ) * cos( radians(videos.lon) - radians(-0.063200)) + sin(radians(51.482600)) * sin( radians(videos.lat))) AS distance FROM videos WHERE rooms >= 1 AND price <= 3500 HAVING distance <= 3) WHERE group by video_id;

或

SELECT video_id,count(video_id),3959 * acos( cos( radians(51.482600) ) * cos( radians( videos.lat ) ) * cos( radians(videos.lon) - radians(-0.063200)) + sin(radians(51.482600)) * sin( radians(videos.lat))) AS distance FROM videos WHERE rooms >= 1 AND price <= 3500 GROUP BY video_id HAVING distance <= 3

赞(0）回复(0）举报 2021-06-18

x7rlezfr2#

您需要这样的子查询来计算行数：

"SELECT count(*) 
 from 
  (SELECT 3959 * acos( cos( radians(51.482600) ) * cos( radians( videos.lat ) ) * cos( radians(videos.lon) - radians(-0.063200)) + sin(radians(51.482600)) * sin( radians(videos.lat))) AS distance 
   FROM videos 
   WHERE rooms >= 1 AND 
         price <= 3500 
   HAVING distance <= 3") as Result;

注意，您必须给子查询一个别名（我称之为result）。
我测试了一个这样的查询，它工作了-所以希望这能解决问题

赞(0）回复(0）举报 2021-06-18

我来回答

即使查询返回结果，mysql计数也不起作用

2条答案

相关问题

热门标签

最新问答