sql - 删除 SQL Server 中的第二行或相同的数字

Question

我正在使用 SQL Server。我使用了这段代码:

 select distinct A.CustomerNo, A.INV_Nr, B.Code, A.Price 
 from INVOICE A.
      INVOICE_LINE B
 where
      A.IVC_Nr = B.IVC_Nr

输出是这样的:

| CustomerNo   |    INV_Nr   |   Code   | Price |
=================================================
| 1100021      |    500897   |   1404   | 2500  |
| 1100021      |    500897   |   1403   | 2500  |
| 1100022      |    500898   |   1405   | 3500  |
| 1100023      |    500899   |   1405   | 3000  |
| 1100023      |    500899   |   1403   | 3000  |

我怎样才能删除第二行，只得到相同数字的第一行，应该是这样的:

| CustomerNo   |    INV_Nr   |   Code   | Price |
=================================================
| 1100021      |    500897   |   1404   | 2500  |
| 1100022      |    500898   |   1405   | 3500  |
| 1100023      |    500899   |   1405   | 3000  |

谢谢，

Answer 1

由于问题出在 SQL Server 2005+ 标签上，您可以在此使用 Common Table Expression 和 Window Function。

WITH recordsList
AS
(
    SELECT  A.CustomerNo, A.INV_Nr, B.Code, A.Price ,
            ROW_NUMBER() OVER (PARTITION BY A.CustomerNo
                                ORDER BY B.Code DESC) rn
    FROM    INVOICE A
            INNER JOIN INVOICE_LINE B
                ON A.IVC_Nr = B.IVC_Nr
)
SELECT  CustomerNo, INV_Nr, Code, Price
FROM    recordsList
WHERE   rn = 1

• SQLFiddle Demo (with slight changes)