使用group by时如何选择最后一个值
我有一张发票"表
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.
相关文章