postgis上的多表查询

发布于 2025-01-01 14:27:53 字数 1972 浏览 2 评论 0原文

在postgis中，我有几个具有相同结构但分别代表Mapserver不同层（1,2,3...）的表，

table1
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name11'     'address11'   102356       1       geom11
        2    'name12'     'address12'   102356       1       geom12
        -    'name1-'     'address1-'   102356       1       geom1-

table2
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name21'     'address21'   102356       2       geom21
        2    'name22'     'address22'   102356       2       geom22
        -    'name2-'     'address2-'   102356       2       geom2-


table3
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name31'     'address31'   102356       3       geom31
        2    'name32'     'address32'   102356       3       geom32
        -    'name3-'     'address3-'   102356       3       geom3-

我想从table1,2,3...获取查询结果如果关键字匹配，说像“名字”这样的名字，如下所示：

输入“名字”，结果就像

results
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name11'     'address11'   102356       1       geom11
        2    'name12'     'address12'   102356       1       geom12
        3    'name21'     'address21'   102356       2       geom21
        4    'name22'     'address22'   102356       2       geom22
        5    'name31'     'address31'   102356       3       geom31
        6    'name32'     'address32'   102356       3       geom32
        -      '-'          '-'          -----       -        --

我只需要从1开始按递增顺序重新编号的gid一样，获取它们的原始gid值似乎没有必要:)

我可以用一个查询语句来实现这一结果吗？又如何呢？有什么好的想法会被应用吗？

谢谢

原文

In postgis, I have a couple of tables with same structure but standing for different layers(1,2,3...) respectively for Mapserver,

table1
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name11'     'address11'   102356       1       geom11
        2    'name12'     'address12'   102356       1       geom12
        -    'name1-'     'address1-'   102356       1       geom1-

table2
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name21'     'address21'   102356       2       geom21
        2    'name22'     'address22'   102356       2       geom22
        -    'name2-'     'address2-'   102356       2       geom2-


table3
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name31'     'address31'   102356       3       geom31
        2    'name32'     'address32'   102356       3       geom32
        -    'name3-'     'address3-'   102356       3       geom3-

I want to get query results from table1, 2, 3... if key word matches, say name like 'name' as follows:

input 'name', results will be like

results
       gid | name     | address       | post code  | layer |  geom
-----------+----------+---------------+------------+-------+------------
        1    'name11'     'address11'   102356       1       geom11
        2    'name12'     'address12'   102356       1       geom12
        3    'name21'     'address21'   102356       2       geom21
        4    'name22'     'address22'   102356       2       geom22
        5    'name31'     'address31'   102356       3       geom31
        6    'name32'     'address32'   102356       3       geom32
        -      '-'          '-'          -----       -        --

I just need gid in increasing order renumbering from 1, fetching their original gid value seems not necessary:)

can I achieve that results with one single query sentence? and how?
any good ideas will be appricated?

thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

素手挽清风 2025-01-08 14:27:53

尝试使用 UNION ALL 和 row_number 函数如下查询：

SELECT 
row_number() over (ORDER BY a.gid, a.layer) AS qid, 
a.name, a.address, a."post code", a.layer, a.geom
FROM
(
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3
)a
WHERE a.name like 'name%'

Try to use UNION ALL and the row_number function like this query:

SELECT 
row_number() over (ORDER BY a.gid, a.layer) AS qid, 
a.name, a.address, a."post code", a.layer, a.geom
FROM
(
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3
)a
WHERE a.name like 'name%'

回复收藏 0 原文

~没有更多了~