sql - 如何在 SQL 中使用内部连接最小化重复行？

Question

我正在处理一个选择语句，但得到了多行。我想按供应商然后承诺排序。

陈述：

SELECT distinct
     APM_MASTER__DISTRIBUTION.Vendor,
     JCM_MASTER__COMMITMENT.Job,
     JCM_MASTER__COMMITMENT.Commitment_Type,
     apm_master__distribution.Commitment_Line_Item,
     JCM_MASTER__COMMITMENT.Contract_Signed_Date,
     JCM_MASTER__COMMITMENT.Delivery_Date,
     JCM_MASTER__COMMITMENT.Scheduled_Start_Date,
     JCM_MASTER__COMMITMENT.Scheduled_Completion_Date,
     APM_MASTER__DISTRIBUTION.Invoice,
     APM_MASTER__DISTRIBUTION.Job,
     APM_MASTER__DISTRIBUTION.DBID,
     APM_MASTER__DISTRIBUTION.JOB,
     APM_MASTER__DISTRIBUTION.Category,
     APM_MASTER__DISTRIBUTION.Cost_Code,
     APT_CURRENT__TRANSACTION.Accounting_Date,
     JCM_MASTER__COMMITMENT.Amount_Paid,
     jcm_master__commitment.Amount_Invoiced,
     JCM_MASTER__COMMITMENT.Commitment,
     APM_MASTER__DISTRIBUTION.Retainage_Held,
     APM_MASTER__DISTRIBUTION.Retainage
from APM_MASTER__DISTRIBUTION
inner join APT_CURRENT__TRANSACTION on
apm_master__distribution.Invoice=APT_CURRENT__TRANSACTION.Invoice
inner join JCM_MASTER__COMMITMENT on 
APM_MASTER__DISTRIBUTION.Commitment=JCM_MASTER__COMMITMENT.Commitment
inner join JCM_MASTER__COMMITMENT_CUSTOM_FIELDS on 
JCM_MASTER__COMMITMENt.Commitment=JCM_MASTER__COMMITMENT_CUSTOM_FIELDS.Commitment
where JCM_MASTER__COMMITMENT_CUSTOM_FIELDS.PushtoJDE='1'

返回 在此处输入图像描述

电流输出

| Vendor | Job       | Commitment_Type | Commitment_Line_Item | Contract_Signed_Date | Delivery_Date | Scheduled_Start_Date | Scheduled_Completion_Date | Invoice    | Job       | DBID           | JOB       | Category | Cost_Code | Accounting_Date | Amount_Paid | Amount_Invoiced | Commitment  | Retainage_Held | Retainage |
|--------|-----------|-----------------|----------------------|----------------------|---------------|----------------------|---------------------------|------------|-----------|----------------|-----------|----------|-----------|-----------------|-------------|-----------------|-------------|----------------|-----------|
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |
| ACME   | 19-585-33 | Subcontract     | 1                    | 00:00.0              | NULL          | NULL                 | 00:00.0                   | 1(1958533) | 19-585-33 | BuildingThings | 19-585-33 | 5        | 9-May     | 00:00.0         | 878055.55   | 924269          | 1958533-S15 | 11367.6        | 11367.6   |

所需的输出 在此处输入图像描述

小贩	工作	承诺类型	Commitment_Line_Item	合同_签署_日期	交货日期	Scheduled_Start_Date	Scheduled_Completion_Date	发票	工作	数据库标识符	工作	类别	成本代码	会计_日期	支付的金额	Amount_Invoiced	承诺	Retainage_Hold	保留
ACME	19-585-33	分包	1	00:00.0	无效的	无效的	00:00.0	1(1958533)	19-585-33	建筑用品	19-585-33	5	May-09	00:00.0	878055.55	924269	1958533-S15	11367.6	11367.6

标签： sql