如何将选择的结果附加到结果集

Question

我有以下表格：

• 表1-节点（我通过DB_LINK访问它）：

  SITE_ID    LATITUDE    LONGITUDE
  ABC123     21.018      -89.711
  CDE456     20.35       -87.349
  FGH789     20.258      -87.406
  ABB987     18.54       -88.302
  CFF546     18.542      -88.273
  GHT553     18.52       -88.311
  

• 表2-LINKS

  ID   SITE_A    SITE_B   STATUS  NAME  LINK_TYPE REGION  ---> Many other fields
  1    ABC123    GHT553
  2    FGH789    CFF546     
  3    CDE456    ABC123        
  4    CFF546    GHT553     
  

• 表3-结果（这是我想要实现的）-无论订单

  LINK_ID   SITE_A_ID   LAT_SITE_A   LON_SITE_A   SITE_B_ID   LAT_SITE_B   LON_SITE_B
  1         ABC123      21.018       -89.711      GHT553      18.52        -88.311
  2         FGH789      20.258       -87.406      CFF546      18.542       -88.273
  3         CDE456      20.35        -87.349      ABC123      21.018       -89.711
  4         CFF546      18.542       -88.273      GHT553      18.52        -88.311
  

（加上其他几个字段，这对我来说意味着没有麻烦）

这就是我尝试过的：

SELECT RES2.*, SAM2.LATITUDE LAT_SITE_B, SAM2.LONGITUDE LON_SITE_B FROM(
    SELECT RES1.*, NOD.LATITUDE LAT_SITE_A, NOD.LONGITUDE LON_SITE_A FROM(
        SELECT ID, SITE_A, SITE_B, STATUS, NAME, LINK_TYPE FROM LINKS
            WHERE SITE_A IS NOT NULL AND SITE_B IS NOT NULL AND REGION IN (8,6)
         )RES1, NODES@NODES_DBLINK NOD WHERE RES1.SITE_A = NOD.SITE_ID
     )RES2, NODES@NODES_DBLINK NOD2 
WHERE RES2.SITE_B = NOD2.SITE_ID;

在SELECT RES1.*之前，一切都很好，但当我添加SELECT RES2.*时，它花费了太长时间而没有返回任何内容。

Answer 1

根据问题的文本部分，此查询将生成您想要的结果：

SELECT links.id AS link_id,
       node_a.site_id AS site_a_id,
       node_a.latitude AS lat_site_a,
       node_a.longitude AS lon_site_a,
       node_b.site_id AS site_b_id,
       node_b.latitude AS lat_site_b,
       node_b.longitude AS lon_site_b
  FROM links
 INNER JOIN nodes@nodes_dblink node_a ON (links.site_a = node_a.site_id)
 INNER JOIN nodes@nodes_dblink node_b ON (links.site_b = node_b.site_id)
 ORDER BY links.id;

从你发布的查询来看，你似乎也有一些其他标准，这可能意味着你想要更像这样的东西：

SELECT links.id AS link_id,
       node_a.site_id AS site_a_id,
       node_a.latitude AS lat_site_a,
       node_a.longitude AS lon_site_a,
       node_b.site_id AS site_b_id,
       node_b.latitude AS lat_site_b,
       node_b.longitude AS lon_site_b
  FROM links
 INNER JOIN nodes@nodes_dblink node_a ON (links.site_a = node_a.site_id)
 INNER JOIN nodes@nodes_dblink node_b ON (links.site_b = node_b.site_id)
 WHERE links.site_a IS NOT NULL
   AND links.site_b IS NOT NULL
   AND links.region IN (8, 6)
 ORDER BY links.id;

希望它能帮助。。。

编辑：如果您的数据库链接有问题，请尝试通过在远程数据库上创建视图或在本地数据库上创建实体化视图，提前通过链接只返回您需要的数据。如果这不可行，那么对照这个查询检查上面查询的相对解释计划，看看它是否更好：

WITH node_data
  AS (SELECT site_id,
             latitude,
             longitude
        FROM nodes@nodes_dblink node
       WHERE EXISTS (SELECT 1
                       FROM links
                      WHERE links.site_a = node.site_id
                         OR links.site_b = node.site_id))
SELECT links.id AS link_id,
       node_a.site_id AS site_a_id,
       node_a.latitude AS lat_site_a,
       node_a.longitude AS lon_site_a,
       node_b.site_id AS site_b_id,
       node_b.latitude AS lat_site_b,
       node_b.longitude AS lon_site_b
  FROM links
 INNER JOIN node_data node_a ON (links.site_a = node_a.site_id)
 INNER JOIN node_data node_b ON (links.site_b = node_b.site_id)
 WHERE links.site_a IS NOT NULL
   AND links.site_b IS NOT NULL
   AND links.region IN (8, 6)
 ORDER BY links.id;