我想选择多个表并选择每个表中的几何 (geom) 和 Name 列并在彼此下方附加。我已经选择了表及其列,如下所示:
SELECT TABLE_NAME COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE (TABLE_NAME LIKE '%HESA' OR
TABLE_NAME LIKE '%HEWH') AND
(COLUMN_NAME = 'geom' AND
COLUMN_NAME = 'Name');
那么你如何取表:
id | geom | Name | id | geom | Name |
____________________ ____________________
1 | geom1 | Name1 | 1 | geom4 | Name4 |
2 | geom2 | Name2 | 2 | geom5 | Name5 |
3 | geom3 | Name3 | 3 | geom6 | Name6 |
并在第一个表格下方附加第二个表格,如下所示:
id | geom | Name |
____________________
1 | geom1 | Name1 |
2 | geom2 | Name2 |
3 | geom3 | Name3 |
1 | geom4 | Name4 |
2 | geom5 | Name5 |
3 | geom6 | Name6 |
我使用 UNION ALL 还是其他什么?
https://www.db-fiddle.com/f/75fgQMEWf9LvPj4xYMGWvA/0
根据您的样本数据:
do
'
declare
r record;
begin
for r in (
SELECT a.TABLE_NAME
FROM INFORMATION_SCHEMA.columns a
JOIN INFORMATION_SCHEMA.columns b on a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = ''geom'' and b.COLUMN_NAME = ''name''
WHERE (a.TABLE_NAME LIKE ''oranges%'' OR a.TABLE_NAME LIKE ''%_db'')
) loop
execute format(''insert into rslt select geom, name from %I'',r.table_name);
end loop;
end;
'
;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句