我正在使用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地理数据类型列
发布评论