SQL with select with group by

SQL with select with group by - 如何获取找到的记录的id？(SQL working select with group by - how to get the id of found records?)

以下SQL语句将所需的记录带出数据库。 另外我想得到那些“分组”记录的id。 如何将此查询与另一个查询相结合以获取所需的ID？

我的表包含lat和lng的设施，其中一些位于同一位置。 我通过查询得到它们如下：

SELECT count(*) FROM facilities GROUP BY lat, lng having count(*) > 1

我怎样才能得到那些记录的facility.id？

Following SQL statements brings the needed records out of the database. In addition I would like to get the id's of those "grouped" records. How can I combine this query with another one to get the needed id's?

My table contains facilities with a lat and lng, some of them are at the same location. I get them by querying as following:

SELECT count(*) FROM facilities GROUP BY lat, lng having count(*) > 1

How can I get facilities.id of those records?

最满意答案

您可以使用分组查询（稍加修改）来获取您感兴趣的lat和lng ，然后将这些结果连接回facilities表以获取其他数据 - 在这种情况下为id ..

就像是：

SELECT a.lat, a.lng, a.locationCount, b.id FROM (SELECT lat, lng, count(*) locationCount FROM facilities GROUP BY lat, lng HAVING count(*) > 1) a INNER JOIN facilities b on a.lat = b.lat and a.lng = b.lng

You can use your grouped query (slightly modified), to get the lat and lng you're interested in, and then join those results back to the facilities table to get the other data - id in this case..

Something like:

SELECT a.lat, a.lng, a.locationCount, b.id FROM (SELECT lat, lng, count(*) locationCount FROM facilities GROUP BY lat, lng HAVING count(*) > 1) a INNER JOIN facilities b on a.lat = b.lat and a.lng = b.lng