时间戳

首页 > 解决方案 > 为每个合同选择具有合同要求的所有代码的供应商

sql - 为每个合同选择具有合同要求的所有代码的供应商

问题描述

我有一个包含“要求代码”（COXA）的合同表和一个包含“批准代码”（VNDAPP）的供应商表。合同可以有任意数量的要求，供应商可以有任意数量的批准。

示例数据：

Contract Requirement (COXA):
CONTR   REQMT
7736    1
7736    10
7737    1
7737    4
7737    6
7738    5
7739    1

Supplier Approval (VNDAPP):
VNDNO   REQMT
10019   1
10020   1
10020   2
10020   10
10021   1
10021   4
10021   5
10021   6

Desired Result:
CONTR   VNDNO
7736    10020
7737    10021
7738    10021
7739    10019
7739    10020
7739    10021

在另一个问题中，当我在查询中指定合同编号时，我收到了有效的回复：

select sa.supplierid
from supplier_approval sa
    where sa.approvalid IN (
    select cr.requirementid
    from contracts_requirement cr
    where cr.contractid = 7736
 )
group by sa.supplierid
having count(distinct sa.approvalid) = (
    select count(*)
    from contracts_requirement cr
    where cr.contractid = 7736
)

问题是我需要为每个合同号找到匹配的供应商。

提前致谢！

标签： sqldb2relational-division

解决方案

您可以使用 CROSS JOIN 生成（合同、供应商、合同要求）的元组，然后使用 LEFT JOIN 将合同要求与供应商批准相匹配：

SELECT
    contract_requirement.contr,
    suppliers.vndno,
    COUNT(contract_requirement.reqmt) AS req_count,
    COUNT(supplier_approval.reqmt) AS app_count
FROM contract_requirement
CROSS JOIN (
    SELECT DISTINCT vndno
    FROM supplier_approval
) AS suppliers
LEFT JOIN supplier_approval ON suppliers.vndno = supplier_approval.vndno AND contract_requirement.reqmt = supplier_approval.reqmt
GROUP BY contract_requirement.contr, suppliers.vndno
HAVING COUNT(contract_requirement.reqmt) = COUNT(supplier_approval.reqmt)

推荐阅读