类型“地理"没有可比性.不能在 GROUP BY 子句中使用 [英] The type "geography" is not comparable. It cannot be used in the GROUP BY clause

问题描述

我有一个这样的查询:

SELECT WorkId, 
RegisterDate , Location

FROM (

SELECT dbo.[Work].WorkId ,
dbo.[Work].RegisterDate , dbo.Look.Location

FROM dbo.Municipality INNER JOIN
dbo.[Work] ON dbo.Municipality .Municipality Id = dbo.[Work].MunicipalityWorkId INNER JOIN
dbo.Look ON dbo.[Work].LookWorkId = dbo.Look.LookId
WHERE (dbo.Look.Location IS NOT NULL) AND Type= 1

) E

GROUP BY WorkId ,RegisterDate , Location

我得到这个错误:

地理"类型没有可比性.不能在 GROUP 中使用BY 子句.

The type "geography" is not comparable. It cannot be used in the GROUP BY clause.

我需要将位置添加到分组依据中，因为我需要在数据库中显示位置.这种情况的解决方案是什么?谢谢.

I need to add Location to Group By, because I need to display Location in the database. What is the solution for this situation? Thanks.

推荐答案

这是一种方法.

使用Stastext 将Location 转换为文本并使用Group by.然后在 Select 中使用 STGeomFromText

Convert the Location to text using Stastext and use it Group by. Then convert it back to geo in Select using STGeomFromText

SELECT WorkId,
       RegisterDate,
       geography::STGeomFromText(Location.STAsText(), 4326)
FROM   (SELECT dbo.[Work].WorkId,
               dbo.[Work].RegisterDate,
               dbo.Look.Location
        FROM   dbo.Municipality
               INNER JOIN dbo.[Work]
                       ON dbo.Municipality.MunicipalityId = dbo.[Work].MunicipalityWorkId
               INNER JOIN dbo.Look
                       ON dbo.[Work].LookWorkId = dbo.Look.LookId
        WHERE  ( dbo.Look.Location IS NOT NULL )
               AND Type = 1) E
GROUP  BY WorkId,
          RegisterDate,
          Location.STAsText() 

引用自此答案

注意: geography 函数区分大小写，应按原样使用

Note : The geography function are CASE sensitive it should be used as it is

• STGeomFromText

• STGeomFromText

STAsText

这篇关于类型“地理"没有可比性.不能在 GROUP BY 子句中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！