問題描述

我有一張發票"表

customer_number | Invoice_Number | Name | Address | Total_Amount

select i"m Making group by customer_number并將其發票的 Total_amount 相加.我仍然想在輸出中顯示他的姓名和地址.轉儲 Invoice_number.

select i"m making group by customer_number and sum the Total_amount of it's invoices. I still want to show his name and address at the output. dumping the Invoice_number.

然而，我更改了客戶的地址甚至姓名，我想根據特定客戶的最后一張發票號碼制作一列最新的address和name.

However Address and even name of a customer my change, i want to make a columns of the latest address and name according to the last invoice_number of the specific customer.

我怎么能那樣做?我正在使用 ms sql

How sould i do that ? I"m usind ms sql

推薦答案

是這樣的:

SELECT customer_number
      ,Name
      ,Address
      ,Total_Amount
FROM
(
    SELECT customer_number
          ,Name
          ,Address
          ,SUM(Total_Amount) OVER (PARTITION BY customer_number) AS Total_Amount
          ,DENSE_RANK() OVER (PARTITION BY customer_number ORDER BY Invoice_Number DESC) AS row_id
    FROM [my_table] 
) DS
WHERE row_id = 1;

使用 OVER 子句我們可以計算每一行的總和.這就像分組，但我們使用 PARTITION BY 而不是 group by 而是每組一行，返回所有行.

Using OVER clause we can calculate the sum for each row. It's like grouping but instead group by we are using PARTITION BY and instead one row per group, all rows are returned.

同時，我們使用排名函數將每個客戶的行按invoce_number desc從最新到第一排序.

At the same time, we are using a ranking function to order the rows of each customer from the latest to the first by invoce_number desc.

最后，我們只需要獲取我們需要的行.

And finally, we just need to get the rows we need.

這篇關于使用group by時如何選擇最后一個值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！