SQL查询一对多的关系加入无重复(SQL query one to many relationshi

我正在运行的查询SQL Server 2008中。我有一个sales表和payments表..有时销售有多种付款方式（部分礼金券+现金部分或部分贷款+部分现金等。）我想等什么做的是列出的销售和付款表中的每个销售。

如果我做一个LEFT JOIN ON sales.SaleID = payments.SaleID我得到重复销售行时有多个匹配的支付行..

所以我一直在做的是让所有的销售和多少匹配的付款行有与计数(SELECT COUNT(*) FROM payments WHERE payments.SaleID = sales.SaleID) AS NumOfPayments 。 然后在我的PHP脚本我支票付款的数量，如果是> 1我然后再次运行查询来获取付款细节。

我想获得的输出会是这个样子

-----------------------------------------------------
| SaleID |  SaleDate  | Amount  | Payments          |
-----------------------------------------------------
|    123 | 2013-07-23 | $ 19.99 | Cash:     $ 19.99 |
|    124 | 2013-07-23 | $  7.53 | Cash:     $  7.53 |
|    125 | 2013-07-23 | $174.30 | Credit:   $124.30 |
|        |            |         | GiftCard: $ 50.00 |
|    126 | 2013-07-23 | $ 79.99 | Cash:     $ 79.99 |
|    127 | 2013-07-23 | $100.00 | Credit:   $ 90.00 |
|        |            |         | Cash:     $ 10.00 |
-----------------------------------------------------

凡售卖125和127有多个付款上市，但销售信息只出现一次，不重复，每次付款。

在sales和payments表如下所示：

Sales                              Payments
---------------------------------  --------------------------------------------
| SaleID |  SaleDate  | Amount  |  | PaymentID | SaleID | PmtMethod |  PmtAmt |
---------------------------------  --------------------------------------------
|    123 | 2013-07-23 | $ 19.99 |  |       158 |    123 |        4  | $ 19.99 |
|    124 | 2013-07-23 | $  7.53 |  |       159 |    124 |        4  | $  7.53 |
|    125 | 2013-07-23 | $174.30 |  |       160 |    125 |        2  | $124.30 |
|    126 | 2013-07-23 | $ 79.99 |  |       161 |    125 |        3  | $ 50.00 |
|    127 | 2013-07-23 | $100.00 |  |       162 |    126 |        4  | $ 79.99 |
---------------------------------  |       163 |    127 |        2  | $ 90.00 |
                                   |       164 |    127 |        4  | $ 10.00 |
                                   --------------------------------------------

我觉得如果我可以做只是SQL它会更快。 有没有办法用纯SQL来完成，而不必使用服务器端代码运行条件查询此。

Answer 1:

我不会混合数据检索和数据显示，这是我认为你是问。 你有某种列来指示支付应首先显示？ 我想是这样的：

SELECT columnlist, 
rn = ROW_NUMBER() OVER (PARTITION BY sales.salesID ORDER BY payment.paymentID)
FROM sales JOIN payments ON sales.salesID=payments.salesID

然后，在你的GUI，只是对第一3列中显示的值，其中RN = 1，并且空出的值，其中RN> 1。

Answer 2:

这可能是更容易做到这一点的接口。

你想要的基本查询：

  select s.saleID, s.SaleDate, s.Amount,
         p.PaymentType, p.PaymentAmount,
         ROW_NUMBER() over (partition by p.SaleId order by p.PaymentAmount desc) as seqnum
  from sales s join
       payments p
       on p.saleID = s.saleId
  order by 1, 2

但是，你想空出的字段。 要做到这一点，你需要的所有字段转换为字符串，然后检查这些都对每个一线SaleId ：

select (case when seqnum > 1 then '' else CAST(SaleId as varchar(255)) end) as SaleId,
       (case when seqnum > 1 then '' else CONVERT(varchar(10), SaleDate, 121) end) as SaleDate,
       (case when seqnum > 1 then '' else '$'+STR(amount, 6, 2) end) as Amount,
       PaymentType, PaymentAmount
from (select s.saleID, s.SaleDate, s.Amount,
             p.PaymentType, p.PaymentAmount,
             ROW_NUMBER() over (partition by p.SaleId order by p.PaymentAmount desc) as seqnum
      from sales s join
           payments p
           on p.saleID = s.saleId
     ) sp
order by SaleId, SaleDate;

这不是SQL是专为操作的类型。 SQL与工作表，所有列具有相同的含义。 在这里，您将引入不同的含义为列，根据其在售的位置。 是的，SQL可以做到这一点。 不，这是不容易的。

Answer 3:

下面的查询会给你所需要的结果，你需要跳过SalesIDToSort列。

  ;with SalesData
  as
  (
    select 
      Sales.SalesID, Sales.SalesDate, Sales.TotalAmount, Payments.PaymentMode, Payments.Amount,
      Row_Number() over(Partition by Sales.SalesID order by Payments.PaymentID) RowNum
    from 
      Payments
      inner join Sales on Payments.SalesID = Sales.SalesID
  )
  select SalesID, SalesDate, TotalAmount, PaymentMode, Amount, SalesID SalesIDToSort
  from 
    SalesData
  where
    SalesData.RowNum = 1
  union all
  select null, null, null, PaymentMode, Amount, SalesID SalesIDToSort
  from 
    SalesData
  where
    SalesData.RowNum > 1
  order by 6

工作演示

Answer 4:

您可以尝试通过功能gourp等业态于：

select salesid, salesDate, Amount, FORMAT(payment, 9999.999) as payments
from FROM payments 
WHERE payments.SaleID = sales.SaleID
group by salesid;

如果您有格式作为可用的功能，这可能工作。

文章来源: SQL query one to many relationship join without duplicates