sql-server - 创建老化报告查询

Question

我需要您的帮助来提出一个可以给我付款帐龄报告的查询。简单的数据如下所示：

---------------------------------------------------------------------------------
| ID | Customer Name | Total Invoice | Paid Invoice | Paid Date | Amount | Aging 
---------------------------------------------------------------------------------
| 1  | David         |     $100      |     $0       |10/10/2018 |  $100  |  1    

| 2  | David         |     $100      |     $80      |15/10/2018 |  $20   |  5    

| 3  | David         |     $100      |     $100     |18/10/2018 |  $0    |  8    

| 4  | Jonh          |     $250      |     $0       |18/10/2018 |  $100  |  0    


---------------------------------------------------------------------------------
| ID | Customer Name | Total Invoice | Paid Invoice | Paid Date | Amount | Aging 
---------------------------------------------------------------------------------
| 1  | David         |     $100      |     $100     |18/10/2018 |  $0    |  8   

| 2  | Jonh          |     $250      |     $  0     |18/10/2018 |  $250  |  0    

标签： sql-server

Answer 1

您需要每个客户的最后一行。一种巧妙的方法是使用row_numberwindows 功能：

SELECT *
FROM   (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY aging DESC) AS rn
        FROM   mytable) t
WHERE  rn = 1