无法在SQL Server中使用所需的值进行选择

Question

创建表

CREATE TABLE dbo.factura
(
     customer_code varchar(20),
     invoice_number char(4),
     line_number char(2), 
     data date
);
    
DROP TABLE dbo.factura;

SELECT * FROM dbo.factura;

填充表：

INSERT INTO dbo.factura VALUES ('ABC', '0012', '01', '2020-10-01');
INSERT INTO dbo.factura VALUES ('ABC', '0012', '02', '2020-11-01');
INSERT INTO dbo.factura VALUES ('ABC', '0012', '03', '2020-11-01');
INSERT INTO dbo.factura VALUES ('ABC', '0013', '08', '2021-01-21');
INSERT INTO dbo.factura VALUES ('ABC', '0013', '09', '2020-09-01');
INSERT INTO dbo.factura VALUES ('SLIK', '0001', '01', '2021-01-01');
INSERT INTO dbo.factura VALUES ('SLIK', '0001', '02', '2020-02-01');

编写一条SQL语句以返回记录集：

CUSTOMER_CODE, INVOICE_NR, LINE_NR

其中，NR_LINE是表中每个客户的每张发票的DATA列中的最新值所在的行号。

我尝试使用自连接，但它不起作用。

你知道如何写这个查询吗？

Answer 1

如果希望按line_number降序对行进行排序，则查询将为：

with cte as (
    select CUSTOMER_CODE, INVOICE_number, LINE_number , row_number()over(partition by customer_code,invoice_number order by LINE_number desc) rn
from factura
)
select CUSTOMER_CODE, INVOICE_number, LINE_number from cte where rn=1

输出：

​

​

如果您希望结果按数据字段的降序排列，则查询将为：

with cte as (
    select CUSTOMER_CODE, INVOICE_number, LINE_number , row_number()over(partition by customer_code,invoice_number order by data desc) rn
from factura
)
select CUSTOMER_CODE, INVOICE_number, LINE_number from cte where rn=1

输出：

​

​