我正在使用SQL Server并创建一个脚本，它将使我的数据库中的工作人员的地理位置不受影响。
SELECT w.display_name，w.geo_location FROM作业j WITH（NOLOCK） INNER JOIN工人w WITH WITH（NOLOCK）w.worker_id = j.worker_id WHERE .....问题在于我想向脚本中添加 GROUP BY w.display_name，w.geo_location ，因为显示了重复的记录。将数据类型为geography的列添加到group by子句会引发错误。
当我加入这个错误时，抛出的错误是：
地理无法比拟。它不能在GROUP BY子句中使用。
有没有办法解决这个问题？根据地理数据类型的需要，我无法将 w.geo_location 转换为 VARCHAR 。
解决方案可以使用 row_number（） p> declare @g geography; set @g = geography :: STGeomFromText（'LINESTRING（-122.360 47.656，-122.343 47.656）'，4326）; 声明@T表（display_name varchar（10），geo_location geography） 插入@T值（'1'，@g）插入@T值（'1'，@g）插入@T值（'1'，@g） 插入@T值（'2'，@ g）插入@T值（'2'，@g） 从中选择display_name，geo_location （ select *， row_number（）over（由display_name分区，geo_location.ToString（）order by（select 0））作为rn 从@T ）作为T 其中rn = 1结果：
display_name geo_location ------------ ------------------------------ -------------------------------------------------- 1 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0 2 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0
I'm using SQL Server and creating a script which will get the workers geographical location out of my database. The script is below.
SELECT w.display_name, w.geo_location FROM jobs j WITH(NOLOCK) INNER JOIN workers w WITH(NOLOCK) ON w.worker_id = j.worker_id WHERE .....The problem is that I want to add GROUP BY w.display_name, w.geo_location to the script as there are duplicate records being shown. Added a column with the data type geography to a group by clause causes an error to be thrown.
The error being thrown when I add this in is:
The type "geography" is not comparable. It cannot be used in the GROUP BY clause.
Is there a way around this? I cannot convert w.geo_location to a VARCHAR as it is needed in the geography data type.
解决方案You can use row_number() something like this.
declare @g geography; set @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326); declare @T table (display_name varchar(10), geo_location geography) insert into @T values ('1', @g) insert into @T values ('1', @g) insert into @T values ('1', @g) insert into @T values ('2', @g) insert into @T values ('2', @g) select display_name, geo_location from ( select *, row_number() over(partition by display_name, geo_location.ToString() order by (select 0)) as rn from @T ) as T where rn = 1Result:
display_name geo_location ------------ -------------------------------------------------------------------------------- 1 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0 2 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0
更多推荐
GROUP BY子句中的SQL地理数据类型列
发布评论