SQL Server 2005 语法帮助 - “根据子查询的最大值选择信息" [英] SQL Server 2005 Syntax Help - "Select Info based upon Max Value of Sub Query"

问题描述

目标位于表格列表下方.

表格:

表:作业

• 作业 ID
• 客户 ID
• 价值
• 年份

表:客户

• 客户 ID
• 客户名称

表格:发票

• 销售金额
• 客户 ID

目标

第 1 部分:(简单)我需要选择所有发票记录并按客户排序(使用 Crystal Reports 放置好)

Part 1: (easy) I need to select all invoice records and sort by Customer (To place nice w/ Crystal Reports)

Select * from Invoice as A inner join Customer as B on A.CustomerID = B.CustomerID

第 2 部分:(困难)现在，我们需要添加两个字段:

Part 2: (hard) Now, we need to add two fields:

• 与该客户的作业关联的作业 ID 具有最大值(自 2008 年起)
• 与该工作相关的价值

伪代码

Select * from 
Invoice as A
inner join Customer as B on A.CustomerID = B.CustomerID
inner join 
(select JobID, Value from Jobs where Job:JobID has the highest value out of all of THIS customer's jobs from 2008) 

总体思路

• 如果我只与一位特定客户打交道，这很容易做到:

• This is fairly easy to do If I am only dealing with one specific customer:

select max(JobId), max(Value) as MaxJobID from Jobs where Value = (select max(Value) from Jobs where CustomerID = @SpecificCustID and Year = '2008') and CustomerID = SpecificCustID and CustomerID = '2008'

select max(JobId), max(Value) as MaxJobID from Jobs where Value = (select max(Value) from Jobs where CustomerID = @SpecificCustID and Year = '2008') and CustomerID = SpecificCustID and CustomerID = '2008'

此子查询确定该客户在 2008 年的最大价值，然后是从 2008 年该客户具有相同价值的潜在多个工作中选择单个工作(不能有欺骗)的问题.

This subquery determines the max Value for this customer in 2008, and then its a matter of choosing a single job (can't have dupes) out of potential multiple jobs from 2008 for that customer that have the same value.

困难

如果我们没有特定的客户 ID 进行比较，会发生什么?如果我的目标是选择所有发票记录并按客户排序，那么此子查询需要访问它当前正在处理的客户.我想这可以通过 JOIN 的 ON 子句有点"完成，但这似乎并没有真正起作用，因为 sub-sub 查询无法访问它.

What happens when we don't have a specific customer ID to compare against? If my goal is to select ALL invoice records and sort by customer, then this subquery needs access to which customer it is currently dealing with. I suppose this can "sort of" be done through the ON clause of the JOIN, but that doesn't really seem to work because the sub-sub query has no access to that.

我显然是头昏眼花.有什么想法吗?

I'm clearly over my head. Any thoughts?

推荐答案

row_number() 函数可以满足您的需求:

The row_number() function can give you what you need:

Select A.*, B.*, C.JobID, C.Value
from 
Invoice as A
inner join Customer as B on A.CustomerID = B.CustomerID
inner join (
   select JobID, Value, CustomerID,
   ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Value DESC) AS Ordinal
   from Jobs
   WHERE Year = 2008
) AS C ON (A.CustomerID = C.customerID AND C.Ordinal = 1)

此查询中的 ROW_NUMBER() 函数将按值降序排列，PARTITION BY 子句将针对 CustomerID 的每个不同值分别执行此操作.这意味着每个客户的最高价值始终为 1，因此我们可以加入该价值.

The ROW_NUMBER() function in this query will order by value in descending order and the PARTITION BY clause will do this separately for each different value of CustomerID. This means that the highest Value for each customer will always be 1, so we can join to that value.

这篇关于SQL Server 2005 语法帮助 - “根据子查询的最大值选择信息"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！