主页 / 问题 / 970133
Accepted
Konstantin78
Asked:2020-04-16 11:29:03 +0000 UTC2020-04-16 11:29:03 +0000 UTC

根据要求获得所需结果的问题

  • 772

有 2 个表（CROSS_T和PLAN_D）（见图）在此处输入图像描述和合同摘要视图。
我无法从这两个表中获得结果，即：通过字段“ PROPOSAL_ID ”从“ PLAN_D ”中获取“ CROSS_T ”中具有合同编号“ NUMBER_CONTRACT ”的
所有唯一“ CONTRACT_ID ”。 那些。 在“ CROSS_T ”中有不同的“ CONTRACT_ID ”值，它们都通过字段“ PROPOSAL_ID ”进行联合。


"CONTRACT_ID" "NUMBER_CONTRACT"  
    45           123/15-19 
    47           бп1/47-19  
    48           бп1/47-19  
    49           бп1/47-19 
    50           бп1/47-19

2）还有关于合约的总结视图

AS
select
c.ID, ct.CLIENT_ID, cl.NAME_ORGANIZATION_FULL as NAME_ORGANIZATION_FULL, cl.TYPE_NP || cl.CITY as NP,
list(distinct(sw.NAME_SORT_WORK), ', ') as LIST_SORT_WORK,
count(distinct(sw.NAME_SORT_WORK)) as COUNT_SORT_WORK,
list(distinct(dp.NAME_SHORT), ', ') as LIST_DEPARTMENT_EXECUTE,
count(distinct(n.DEPARTMENT_ID_EXECUTE)) as COUNT_DEPARTMENT_EXECUTE,
list(distinct(p.NUMBER_IN), ', ') as LIST_PROPOSAL_NUMBER_IN,
list(distinct(p.ID), ', ') as LIST_PROPOSAL_ID
from CONTRACT c
  join CROSS_T ct on ct.CONTRACT_ID = c.ID
  join CLIENT cl on ct.CLIENT_ID = cl.ID
  left join NC n on n.CONTRACT_ID = c.ID
  left join DEVICES d on n.DEVICE_ID = d.ID
  left join SORT_WORK sw on d.SORT_WORK_ID = sw.ID
  left join DEPARTMENT dp on dp.ID = n.DEPARTMENT_ID_EXECUTE
  left join PROPOSAL p on p.ID = ct.PROPOSAL_ID
group by c.ID, ct.CLIENT_ID, cl.NAME_ORGANIZATION_FULL, cl.TYPE_NP, cl.CITY
;

将第一个问题（2 个字段）的结果连接到此视图（其中“ CONTRACT_ID ”（来自第一个问题）=“ с.ID ”（来自第二个问题））也不错。
PS这个观点也有“加入CROSS_T ”

提前致谢。

sql

1 个回答

  1. Best Answer

    应该有 2 个字段的结果（合同 ID 和编号）

    SELECT DISTINCT t1.contract_id, t3.number_contract
FROM cross_t t1
JOIN cross_t t2 ON t1.proposal_id = t2.proposal_id
JOIN plan_d t3 ON t2.contract_id = t3.contract_id
WHERE t1.contract_id IS NOT NULL
ORDER BY t1.contract_id;
    • 1

相关问题