SQL查询一对多关系联接，无重复 [英] SQL query one to many relationship join without duplicates

问题描述

我正在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来完成此任务，而不必使用服务器端代码来运行条件查询.

解决方案

我不会混合使用数据检索和数据显示，这就是我想问的问题.您是否有某种列指示应该首先显示哪个付款?我在想类似的东西:

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

然后，在您的GUI中，仅显示RN = 1的前3列的值，并在RN> 1的地方空白.

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.

解决方案

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.

这篇关于SQL查询一对多关系联接，无重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！