棘手的SQL与Sql几何字段不同 [英] Tricky sql distinct with Sql Geometry field

问题描述

我有一个与 Sql Geometry 类型不兼容的sql语句，所以我试图修改它以使其正常工作。

I have an sql statement that is not compatible with the Sql Geometry type so i am trying to revamp it to make it work.

它的基本要点如下：

Select distinct A,B,C,D,SHAPE FROM TABLE1 UNION SELECT A,B,C,D,SHAPE FROM TABLE2

在两个不同的（但相似的）表上有一个复杂的不同的语句是联合的。

So the results of a complex distinct statement on two different (but similar) tables is unioned.

问题在于 SQL几何体类型（SHAPE字段）不能包含在不同的语句中，因为它不是可比的类型。

The problem is that the SQL Geometry type (the SHAPE field) cannot be included in a distinct statement because it is not a comparable type.

我可以移除 SHAPE 字段来自参与联合的两个子查询。但是我想在分析这些子查询后重新找回它。
如果我知道列A的值，我可以得到 SHAPE 字段。

I'm okay with removing the SHAPE field from both of the subqueries that take part in the union. But i want to get it back after those subqueries are parsed. I can get the SHAPE field if i know the value of column A.

所以我的问题是：我如何从两个子查询中的不同语句中删除一个字段，然后通过将该字段加入到另一个字段中，将结果集中的字段返回（与其余字段一起）（ A）？

So my question is: how can i remove a field from the distinct statements in the two subqueries but then get that field back (along with the rest of the fields) in the result set by joining it in on another column (A)?

推荐答案

您可以将其转换为VARBINARY（MAX），然后退回。

You can cast to VARBINARY(MAX), and then cast back.

create table tbl1 ( ID int, a int, b char(3), d geometry );
create table tbl2 ( ID int, a int, b char(3), d geometry, other float );
insert tbl1 (ID, a, b, d) values
    (1, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)),
    (2, 3, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)),
    (3, 4, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)),
    (4, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 250 0, 150 150, 0 150, 0 0))', 0)),
    (5, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 350 0, 150 150, 0 150, 0 0))', 0))
insert tbl2 (ID, a, b, d) values
    (6, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0)), -- same as tbl1.ID=1
    (7, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 950 0, 150 150, 0 150, 0 0))', 0)),
    (8, 2, 3, geometry::STGeomFromText('POLYGON ((0 0, 350 0, 150 150, 0 150, 0 0))', 0)); -- same as tbl1.ID=5

SELECT A, B, cast(c as geometry) c
FROM
(
    Select A,B,cast(D as varbinary(max)) c FROM tbl1
    UNION
    SELECT A,B,cast(D as varbinary(max)) FROM tbl2
) X;

这篇关于棘手的SQL与Sql几何字段不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！