GROUP BY子句中的SQL地理数据类型列 [英] SQL Geography data type column in GROUP BY clause
问题描述
我正在使用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 = 1
Result:
display_name geo_location
------------ --------------------------------------------------------------------------------
1 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0
2 0xE610000001148716D9CEF7D34740D7A3703D0A975EC08716D9CEF7D34740CBA145B6F3955EC0
这篇关于GROUP BY子句中的SQL地理数据类型列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!