如何在whereand子句中使用sql选择数据?

我有这段mysql代码,工作正常,但我想知道如何才能避免做两次RAW数据计算,这是我的代码。

SELECT `users`.*, ( 3959 * ACOS(COS(RADIANS(43.9743)) *
  COS(RADIANS(locations.lat)) * 
  COS( RADIANS(-75.9122) - RADIANS(locations.LONG)) +
                              SIN(RADIANS(43.9743)) *
  SIN(RADIANS(locations.lat) )) ) AS distance 
FROM   `users` 
INNER JOIN `addresses` ON `users`.`id` = `addresses`.`user_id` 
LEFT JOIN `locations` ON `addresses`.`zip` = `locations`.`zip` 
WHERE  `type` = 'doctor' 
  AND ( 3959 * ACOS(COS(RADIANS(43.9743)) * COS(RADIANS(locations.lat)) *
    COS( RADIANS(-75.9122) - RADIANS(locations.LONG)) +
    SIN(RADIANS(43.9743)) * SIN(RADIANS(locations.lat) )) ) < 100
  AND `users`.`deleted_at` IS NULL 
ORDER BY distance ASC
LIMIT  15 OFFSET 0 

如你所见,我用了两次这样的计算方法。( 3959 * ACOS(COS(RADIANS(43.9743)) * COS(RADIANS(locations.lat)) * COS( RADIANS(-75.9122) - RADIANS(locations.LONG)) + SIN(RADIANS(43.9743)) * SIN(RADIANS(locations.lat) )) ) 关于 AND选择 声明。

我试过让 AND distance < 100 但我得到了一个错误,说列不存在。

感谢任何帮助

解决方案:

MySQL扩展使用了 having 子句,这是解决这个问题的最好方法。

SELECT u.*,
       ( 3959 * ACOS(COS(RADIANS(43.9743)) * COS(RADIANS(l.lat) ) * 
                     COS( RADIANS(-75.9122) - RADIANS(l.LONG)) +
                     SIN(RADIANS(43.9743)) * SIN(RADIANS(l.lat))
                    )
       ) AS distance 
FROM `users` u INNER JOIN
     `addresses` a 
     ON u.`id` = a.`user_id` LEFT JOIN
     `locations` l
     ON a.`zip` = l.`zip` 
WHERE `type` = 'doctor' AND u.`deleted_at` IS NULL 
HAVING distance < 100
ORDER BY distance ASC
LIMIT  15 OFFSET 0 

这样做比较好是因为MySQL有将子查询和CTE具体化的习惯,这会增加额外的开销。

给TA打赏
共{{data.count}}人
人已打赏
解决方案

JTable自动读取空值问题

2022-4-22 19:09:56

解决方案

如何将excel导入到datagrid,然后通过db值进行过滤。

2022-4-22 19:09:58

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索