SQL query one to many relationship join without du

问题:

I'm running queries in SQL Server 2008.. I have a sales table and a payments table.. sometimes a sale has multiple methods of payment (part giftcard + part cash or part credit + part cash etc..) so what I want to do is list the sales and the payments for each sale in a table.

If I do a LEFT JOIN ON sales.SaleID = payments.SaleID I get duplicate sales rows when there are more than one matching payment rows..

So what I have been doing is getting all the sales and a count of how many matching payment rows there are with (SELECT COUNT(*) FROM payments WHERE payments.SaleID = sales.SaleID) AS NumOfPayments. Then in my PHP script I check the number of payments and if it is > 1 I then run another query to get the payment details.

The output I am trying to get would look something like this

-----------------------------------------------------
| 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 |
-----------------------------------------------------

Where sale 125 and 127 have multiple payments listed but the sale information only appears once and is not duplicated for each payment.

The sales and payments tables look like this:

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 |
                                   --------------------------------------------

I feel like if I can do it with just SQL it will be faster. Is there a way to accomplish this with pure SQL instead of having to use server side code to run conditional queries.

回答1:

I wouldn't mix data retrieval and data display, which is what I think you are asking about. Do you have some sort of column to indicate which payment should be displayed first? I'm thinking something like:

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

Then, in your GUI, just display the values for the first 3 columns where RN = 1, and blank out the values where RN > 1.

回答2:

It is probably easier to do this in the interface.

The basic query you want is:

  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

However, you are trying to blank-out fields. To do this, you need to convert all the fields to strings and then check if which are on the first line for each 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;

This is not the type of operation that SQL is designed for. SQL works with tables, where all columns have the same meaning. Here, you are introducing a different meaning for the column, depending on its position in the sale. Yes, SQL can do it. No, it is not easy.

回答3:

Following query will give the results you need, you need to skip the SalesIDToSort column.

  ;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

Working demo

回答4:

You could try a gourp by function such as Format to:

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

this might work if you have FORMAT as an available function.