SQL Server 2005语法帮助-“基于子查询的最大值选择信息”。

admin

SQL Server 2005语法帮助-“基于子查询的最大值选择信息”。

sql

该目标是表列表的下方。

Tables:

Table: Job

JobID
CustomerID
Value
Year

Table: Customer

CustomerID
CustName

Table: Invoice

SaleAmount
CustomerID

目标

第1部分：（简单）我需要选择所有发票记录并按客户排序（以放置带有Crystal Reports的页面）

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

第2部分：（困难）现在，我们需要添加两个字段：

与具有最大值的该客户的作业关联的JobID（自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)

一般思想

如果我只与一个特定的客户打交道，这很容易做到：

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年以来的多个潜在工作中选择一个工作（不能重复）。

难点

当我们没有可比较的特定客户ID时，会发生什么？如果我的目标是选择所有发票记录并按客户排序，则此子查询需要访问当前正在处理的客户。我想这可以通过JOIN的ON子句“完成”，但实际上似乎不起作用，因为子子查询无法访问它。

我显然在头上。有什么想法吗？

阅读 146

2021-06-07

共1个答案

admin

该ROW_NUMBER（）函数可以给你需要的东西：

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，因此我们可以加入该价值。

2021-06-07