“地理”类型是没有可比性的。不能在 GROUP BY 子句中使用

我有一个这样的查询：

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 条款。

我需要将位置添加到分组依据，因为我需要在数据库中显示位置。针对这种情况有什么解决办法呢？谢谢。

这是一种方法。

转换Location使用文本Stastext并使用它Group by。然后将其转换回 geo inSelect 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() 

从这里引用answer https://stackoverflow.com/questions/10121403/sql-geography-data-type-column-in-group-by-clause

Note :地理函数是区分大小写应该按原样使用

• STGeomFromText

• STAsText