消除联接SQL查询中的重复项

Question

我想知道是否有任何方法可以消除重复的行信息，即当一个表中有两个项，而另一个表中只有一个项时，它是为了填充结果中的空间而创建的。我尝试过不同的连接和一组，但这两种方法都没有效果。我想我需要某种嵌套的子查询，或者某个地方的限制或rownum =1条件。谢谢你提供的任何帮助。

我的代码：

select distinct
    Driver_Extra_Pay.Order_ID as 'Order',
    Deduct_Code_ID as 'D/E Code',
    Short_Desc as 'Pay Description',
    Driver_Extra_Pay.Amount as 'Pay Amount',
    Driver_Extra_Pay.Amount_D as 'Pay Date',
    Other_Charge.Descr as 'Charge Description',
    Other_Charge.Amount as 'Charge Amount',
    Other_Charge.Amount_D as 'Charge Date'

from
    Driver_Extra_Pay
inner join
    Other_Charge on Other_Charge.Order_ID = Driver_Extra_Pay.Order_ID

where Driver_Extra_Pay.Amount_D between '03/29/2016' and '03/30/2016'

order by Driver_Extra_Pay.Order_ID

这就是我得到的结果。它是这样写的，用作比较工具，但在只有1的情况下，它提供了双倍的"Lumper“条目。

​

​

我希望它在输出上看起来像这样，但是我不知道是什么编码来实现它。

​

​

Answer 1

您想要显示数据的方式不会在SQl中工作。如果您想在表示层中显示它，可以这样显示它。不过，由于其他收费似乎与司机的额外薪酬并无直接关系，或许你真正需要的是获得适当的数据。这将返回两个表中该日期范围的所有费用，以便以后在需要时可以更容易地将它们加在一起。

    SELECT * 
    FROM 
    (select distinct
        Driver_Extra_Pay.Order_ID as 'Order',
        Deduct_Code_ID as 'D/E Code',
        Short_Desc as 'Description',
        Driver_Extra_Pay.Amount as 'Amount',
        Driver_Extra_Pay.Amount_D as 'Date'
    from
        Driver_Extra_Pay
    where Driver_Extra_Pay.Amount_D between '03/29/2016' and '03/30/2016'
    UNION ALL
    select distinct
       Other_Charge.Order_ID as 'Order',
       'Other' as 'D/E Code',
       Other_Charge.Descr ,
       Other_Charge.Amount,
       Other_Charge.Amount_D,
    from
        Other_Charge 
    where  Other_Charge .Amount_D between '03/29/2016' and '03/30/2016') a
    order by [Order]

此查询将报告没有相应的司机额外薪资记录的其他费用(您当前的查询没有此记录)。如果你需要这个，那就试试这个：

SELECT * 
FROM 
(select distinct
    Driver_Extra_Pay.Order_ID as 'Order',
    Deduct_Code_ID as 'D/E Code',
    Short_Desc as 'Description',
    Driver_Extra_Pay.Amount as 'Amount',
    Driver_Extra_Pay.Amount_D as 'Date'
from
    Driver_Extra_Pay
where Driver_Extra_Pay.Amount_D between '03/29/2016' and '03/30/2016'
UNION ALL
select distinct
   Other_Charge.Order_ID as 'Order',
   'Other' as 'D/E Code',
   Other_Charge.Descr ,
   Other_Charge.Amount,
   Other_Charge.Amount_D,
from
    Driver_Extra_Pay
inner join
    Other_Charge on Other_Charge.Order_ID = Driver_Extra_Pay.Order_ID
where Driver_Extra_Pay.Amount_D between '03/29/2016' and '03/30/2016') a
order by [Order]