[ABI 5.2] 2个自定义SQL数据源 如何关联

跨库分析可以看看这个方法https://bbs.esensoft.com/thread-134945-1-1.html

我按照这个方法可以关联了 但是关联结果 数据不正确..

我有两张表 简单来讲 就是表1里有一个字段为表2的id  我要根据这个id 查出来表2对应行数据的一些字段并进行展示  现在不管我用左连接 右连接 还是内连接 还是全连接 结果都是错的，正确的来讲应该是左连接 表一作为主表 但实在不知道哪里设置不对了

表1的id字段和表2的id连接方法看下

这是我的大概的结构

截图202104071106428366.png (55.42 KB, 下载次数: )

下载附件

2021-4-7 11:06 上传

我选择的是根据合同号向下浮动，但是实际的数据合同号在这个表里并不是唯一的

截图202104071108381217.png (27.33 KB, 下载次数: )

下载附件

2021-4-7 11:08 上传

截图202104071109096410.png (33.82 KB, 下载次数: )

下载附件

2021-4-7 11:09 上传

SELECT
  a.B3 AS B3,
  a.C3 AS C3,
  a.D3 AS D3,
  a.F3 AS F3,
  a.H3 AS H3,
  a.I3 AS I3,
  a.J3 AS J3,
  a.K3 AS K3,
  a.N3 AS N3,
  a.O3 AS O3,
  a.P3 AS P3,
  a.Q3 AS Q3,
  a.R3 AS R3,
  a.S3 AS S3,
  a.T3 AS T3,
  a.U3 AS U3,
  a.V3 AS V3,
  a.W3 AS W3,
  a.X3 AS X3,
  a.Y3 AS Y3,
  a.AA3 AS AA3,
  a.AB3 AS AB3,
  a.AC3 AS AC3,
  a.AE3 AS AE3,
  a.AF3 AS AF3,
  a.AG3 AS AG3,
  a.AH3 AS AH3,
  a.AI3 AS AI3,
  a.AJ3 AS AJ3,
  a.AK3 AS AK3,
  a.AL3 AS AL3,
  a.AM3 AS AM3,
  a.AN3 AS AN3,
  a.AO3 AS AO3,
  a.AP3 AS AP3,
  a.AQ3 AS AQ3,
  b.G3 AS G3,
  b.Z3 AS Z3,
  a.E3 AS E3
FROM
  (SELECT
    a.vendor_name AS B3,
    a.organization_name AS C3,
    a.account_name AS D3,
    a.inquiry_type AS F3,
    a.is_standard_contract_text AS H3,
    a.container_type AS I3,
    a.goods_name AS J3,
    a.service_type AS K3,
    a.start_place AS N3,
    a.end_place AS O3,
    a.job_place_name AS P3,
    a.total_distance AS Q3,
    a.base_price AS R3,
    a.price_unit AS S3,
    a.settlement_method AS T3,
    a.payment_method AS U3,
    a.payment_type AS V3,
    a.bond AS W3,
    a.start_period AS X3,
    a.end_period AS Y3,
    a.remark AS AA3,
    a.created_at AS AB3,
    a.created_by AS AC3,
    a.sender_name AS AE3,
    a.recipient_name AS AF3,
    a.courier_type AS AG3,
    a.sender_at AS AH3,
    a.courier_express_code AS AI3,
    a.courier_number AS AJ3,
    a.recycle_name AS AK3,
    a.recycle_at AS AL3,
    a.recycle_type AS AM3,
    a.courier_express_code AS AN3,
    a.recycle_number AS AO3,
    a.is_scan_upload AS AP3,
    a.is_courier_invalid AS AQ3,
    a.contract_code AS E3
  FROM
    ESISVEND_CONT a) a
INNER JOIN
    (SELECT
      a.fee_project_name AS G3,
      a.tax_rate AS Z3,
      b.contract_code AS E3
    FROM
      ESISBASE_FEE_PROJECT_INFO a
      INNER JOIN ESISVEND_CONT b
        ON (b.FEE_ITEM = a.ID)) b
    ON (a.E3 = b.E3)



测试了一晚上，这2个INNER JOIN我现在可以控制了，但是不论是 左连接 右连接结果都是错的，原因就在于b.contract_code这个字段的值在表中不是唯一的，他这个原理是先用浮动字段关联出对应B表的字段，然后再用主表去连接这个结果集，如果这个b.contract_code不唯一好像就会有问题，有其他办法解决吗？


我想要的结果就是类型以下这样


SELECT
  a.B3 AS B3,
  a.C3 AS C3,
  a.D3 AS D3,
  a.F3 AS F3,
  a.H3 AS H3,
  a.I3 AS I3,
  a.J3 AS J3,
  a.K3 AS K3,
  a.N3 AS N3,
  a.O3 AS O3,
  a.P3 AS P3,
  a.Q3 AS Q3,
  a.R3 AS R3,
  a.S3 AS S3,
  a.T3 AS T3,
  a.U3 AS U3,
  a.V3 AS V3,
  a.W3 AS W3,
  a.X3 AS X3,
  a.Y3 AS Y3,
  a.AA3 AS AA3,
  a.AB3 AS AB3,
  a.AC3 AS AC3,
  a.AE3 AS AE3,
  a.AF3 AS AF3,
  a.AG3 AS AG3,
  a.AH3 AS AH3,
  a.AI3 AS AI3,
  a.AJ3 AS AJ3,
  a.AK3 AS AK3,
  a.AL3 AS AL3,
  a.AM3 AS AM3,
  a.AN3 AS AN3,
  a.AO3 AS AO3,
  a.AP3 AS AP3,
  a.AQ3 AS AQ3,
  b.G3 AS G3,
  b.Z3 AS Z3,
  a.E3 AS E3
FROM
  (SELECT
    a.vendor_name AS B3,
    a.organization_name AS C3,
    a.account_name AS D3,
    a.inquiry_type AS F3,
    a.is_standard_contract_text AS H3,
    a.container_type AS I3,
    a.goods_name AS J3,
    a.service_type AS K3,
    a.start_place AS N3,
    a.end_place AS O3,
    a.job_place_name AS P3,
    a.total_distance AS Q3,
    a.base_price AS R3,
    a.price_unit AS S3,
    a.settlement_method AS T3,
    a.payment_method AS U3,
    a.payment_type AS V3,
    a.bond AS W3,
    a.start_period AS X3,
    a.end_period AS Y3,
    a.remark AS AA3,
    a.created_at AS AB3,
    a.created_by AS AC3,
    a.sender_name AS AE3,
    a.recipient_name AS AF3,
    a.courier_type AS AG3,
    a.sender_at AS AH3,
    a.courier_express_code AS AI3,
    a.courier_number AS AJ3,
    a.recycle_name AS AK3,
    a.recycle_at AS AL3,
    a.recycle_type AS AM3,
    a.courier_express_code AS AN3,
    a.recycle_number AS AO3,
    a.is_scan_upload AS AP3,
    a.is_courier_invalid AS AQ3,
    a.contract_code AS E3
    b.fee_project_name AS G3,
    b.tax_rate AS Z3

  FROM
    ESISVEND_CONT a
INNER JOIN
    ESISBASE_FEE_PROJECT_INFO b
    ON (a.FEE_ITEM = b.ID)
) temp

您两个表关联式fee_item和id 那浮动的时候也应该用这两个字段浮动

有两个问题：
1，我表格中其实最终展示的时候 不需要展示 fee_item和id字段

2，我这行数据要与4个表进行关联 关联的字段都不同  每个关联需要查询的数据都类似 这种要如何处理呢？一行数据可以加多个浮动吗 ?

1.不需要 可以浮动出来 然后隐藏掉。
2.是的  需要多个浮动 因为本质是表格计算查询的时候先查关联关系再根据关联关系查数据。您这里的需求有多张表 可以试试直接根据关联关系建立主题表 见关联主题表，这样的情况下可以建立后的表可根据表其他字段浮动

截图202104071426136419.png (39.16 KB, 下载次数: )

下载附件

2021-4-7 14:26 上传

多个浮动 我之前设置过 如果一列有多个浮动 那浮动范围如何设置呢？ 我之前设置的时候 两个好像不能互相包含（覆盖）？

还有一个新的问题，这是我建立的主题表主表，

截图202104071431431867.png (63.47 KB, 下载次数: )

下载附件

2021-4-7 14:31 上传

这两个字段是日期型的 无时分秒

截图202104071432265951.png (5.59 KB, 下载次数: )

下载附件

2021-4-7 14:32 上传

但是到最终计算结果的表格里就带了时分秒

截图202104071433403150.png (14.28 KB, 下载次数: )

下载附件

2021-4-7 14:33 上传

我在表格里是直接取的这个主题表的这两个字段 没有做任何处理

这个表关联应该不好弄，我的主表其实也不是一个单表，我的主表就是一个SQL汇聚了同一个库里的各种表进行关联查询后的一个结果集 ，然后需要和另外一个库的各种类似与 维度表之类的东西进行关联 带出各种额外的信息

浮动范围这个可以参考帮助文档https://help.esensoft.com/doc-view-213.html
新问题重新提贴哈