使用 HAVING 和 WHERE 进行查询

发布于 2024-10-11 07:42:59 字数 627 浏览 8 评论 0原文

我正在尝试创建一个结合以下两个查询的单个查询。

SELECT 
  campgroundid, 
  ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + 
    sin( radians(37) ) * sin( radians( lat ) ) ) ) 
  AS distance 
FROM campground 
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

SELECT * FROM campground WHERE type='private' AND wifi = 1

我尝试将它们放入 IN 中，但它返回了一个语法错误，我不知道如何修复。我尝试删除 HAVING 并组合查询，但它说它无法弄清楚 distance 是什么。任何帮助表示赞赏。谢谢。

输出：[campgroundid、名称、类型、 wifi，距离] [1，ABC营地，私人， 1, 1.34 mi] [2，XYZ 营地，私人，1， 4.44 英里]

原文

I'm trying to create a single query that will combine the following two queries.

SELECT 
  campgroundid, 
  ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + 
    sin( radians(37) ) * sin( radians( lat ) ) ) ) 
  AS distance 
FROM campground 
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

SELECT * FROM campground WHERE type='private' AND wifi = 1

I tried putting them into an IN but it returned a syntax error I couldn't figure out how to fix. I tried just removing the HAVING and combining the queries, but then it says it isn't able to figure out what distance is. Any help is appreciated. Thanks.

OUTPUT: [campgroundid, name, type,
wifi, distance] [1,camp ABC, private,
1, 1.34 mi] [2,camp XYZ, private, 1,
4.44 mi]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

樱娆 2024-10-18 07:43:00

未给出的信息包括露营地和标记表之间的关系。我们需要该信息来了解如何连接表。

此外，HAVING 需要 GROUP BY（它的操作类似于 GROUP BY 聚合结果上的 WHERE 子句）。如果您不聚合标记中的行，则需要 WHERE，而不是 HAVING。

据猜测，您想要这样的东西：

 SELECT id (expression) as distance FROM markers
      WHERE distance < 25 AND 
        campground_id IN (SELECT id FROM campgrounds WHERE type = 'private' AND wifi = 1)

编辑：反映只有一张桌子的新信息。

不能在 WHERE 子句中使用列别名。我猜您知道这一点，并且也知道您可以在 HAVING 中使用它们，这就是为什么您尝试用 HAVING 代替 WHERE。为此，您必须重写为 GROUP BY 查询：

SELECT campgroundid, name, private, wifi, 
   ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + 
    sin( radians(37) ) * sin( radians( lat ) ) ) ) 
    AS distance 
FROM campground 
GROUP BY campgroundid 
HAVING distance < 25 AND type='private' AND wifi = 1
ORDER BY distance LIMIT 0 , 20;

只要campgroundid 是唯一的（因为其他值将来自该 id 的唯一记录），这将起作用。

Among the information not given is how the campground and markers tables are related. We'll need that info to know how to JOIN the tables.

Also, HAVING requires GROUP BY (it operates like a WHERE clause on the aggregated results of GROUP BY). If you're not aggregating the rows in markers, you want WHERE, not HAVING.

At a guess, you want something like this:

 SELECT id (expression) as distance FROM markers
      WHERE distance < 25 AND 
        campground_id IN (SELECT id FROM campgrounds WHERE type = 'private' AND wifi = 1)

EDIT: Reflecting the new info that there's only one table.

You cannot use column ALIASes in a WHERE clause. I'm guessing you know that, and also know that you can use them in HAVING, which is why you're trying to swap HAVING in place of WHERE. To do that, you'll have to rewrite as a GROUP BY query:

SELECT campgroundid, name, private, wifi, 
   ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + 
    sin( radians(37) ) * sin( radians( lat ) ) ) ) 
    AS distance 
FROM campground 
GROUP BY campgroundid 
HAVING distance < 25 AND type='private' AND wifi = 1
ORDER BY distance LIMIT 0 , 20;

This will work as long as campgroundid is unique (since the other values will then come from the only record for this id).

回复收藏 0 原文

腹黑女流氓 2024-10-18 07:43:00

看起来应该是 WHERE distance << 25，因为 HAVING 用于诸如 HAVING MAX(distance) 之类的查询25 和其他聚合函数。

回复收藏 0 原文

金橙橙 2024-10-18 07:43:00

我想这值得尝试（就像将第二个sql中的where子句添加到第一个sql中一样简单）

SELECT 
  ... AS distance 
FROM campground 
WHERE type='private' AND wifi = 1
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

I guess this is worth to try (as simple as add the where clause from second sql into first)

SELECT 
  ... AS distance 
FROM campground 
WHERE type='private' AND wifi = 1
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

回复收藏 0 原文

很糊涂小朋友 2024-10-18 07:43:00

选择
露营地 ID,
( 3959 * acos( cos( 弧度(37) ) * cos( 弧度( 纬度 ) ) *
cos( 弧度( lng ) - 弧度(-122) ) +
sin( 弧度(37) ) * sin( 弧度( 纬度 ) ) ) )
AS距离
从露营地出发
WHERE type='private' AND wifi = 1
按距离 LIMIT 0 , 20 排序
距离< 25

这可能有用

回复收藏 0 原文

复古式 2024-10-18 07:43:00

如果您的问题是使用 JOIN 逻辑从两个表中获取 WHERE 子句。
然后您必须将该值包含在 SELECT 列表中。
例如，
选择 USER.UserName、USER.UserId、LOC.id、LOC.lat、LOC.lon，( 3959 * acos( cos( 弧度('123.1210022') ) * cos( 弧度( lat ) ) * cos( 弧度( lon ) - 弧度('21.200001') ) + sin( 弧度('123.1210022') ) * sin( 弧度( 纬度 ) ) ) ) AS 距离
FROM 用户位置 LOC，用户 USER
距离< '1' AND LOC.id = USER.UserId
按距离排序
LIMIT 0 , 20

如果您在 Select 列表中遗漏了 USER.UserId，则您将无法在 WHERE 子句中使用 LOC.id = USER.UserId。

回复收藏 0 原文

~没有更多了~