mysql - 同一个查询中的 Group by 和 Having 子句

Question

架构:

place(pid, name, type, lat, lng, deleted)

---

I want to select count of places, grouping them by their type and having a distance of < 10 KM from a particular lat, lng

查询:

SELECT count(p.type) as count
FROM (place as p)
where p.deleted != 1
 and p.pid in
    (
    select p2.pid,
            IFNULL(acos(sin((18.5236 *pi()/180)) * sin((p2.lat*pi()/180))+cos((18.5236 *pi()/180)) * cos((p2.lat *pi()/180)) * cos(((73.8478 - p2.lng)*pi()/180))) * 6371.009, 0) AS distance
    from place p2
    having `distance` < 10
    )
group by p.type;

错误:

Operand should contain 1 column(s)

那是因为我在子选择查询中选择了 2 列，即 pid 和 distance。但是如果不使用第二个选择列，我该如何计算距离。

Answer 1

像这样重写你的脚本

SELECT  count(p.type) AS count,
        -- remove this if not necessary
        SUM(IFNULL(acos(sin((18.5236 *pi()/180)) * sin((p.lat*pi()/180))+cos((18.5236 *pi()/180)) * cos((p.lat *pi()/180)) * cos(((73.8478 - p.lng)*pi()/180))) * 6371.009, 0)) AS distance

FROM place AS p
WHERE p.deleted != 1
GROUP BY p.type
HAVING SUM(IFNULL(acos(sin((18.5236 *pi()/180)) * sin((p.lat*pi()/180))+cos((18.5236 *pi()/180)) * cos((p.lat *pi()/180)) * cos(((73.8478 - p.lng)*pi()/180))) * 6371.009, 0)) < 10