gpt4 book ai didi

mysql - 如何在 where/and 子句中使用 sql 选择数据

转载 作者:行者123 更新时间:2023-12-04 00:20:07 25 4
gpt4 key购买 nike

我有这个运行良好的 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) )) )ANDSELECT 语句上。

我试过 AND distance < 100但我收到一条错误消息,说列不存在。

感谢任何帮助

最佳答案

您可以将计算列放置在可从中访问指定列的子查询(特别是“表表达式”)中。

例如:

select *
from ( -- the definition of table expression "x" starts
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`
) x -- the definition of table expression "x" ends
WHERE `type` = 'doctor'
AND distance < 100 -- here we use the "distance" column
AND `users`.`deleted_at` IS NULL
ORDER BY distance ASC
LIMIT 15
OFFSET 0

在 MySQL 8.x 中,您可以使用 CTE(公用表表达式),如:

with
x as (
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`
)
SELECT *
FROM x
WHERE `type` = 'doctor'
AND distance < 100
AND `users`.`deleted_at` IS NULL
ORDER BY distance ASC
LIMIT 15
OFFSET 0

关于mysql - 如何在 where/and 子句中使用 sql 选择数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/61372741/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com