sql - 从 SQL 中获取单个 MAX 值 + 另一个列值的最佳方法

Question

我定义了以下三个表:

table 'A':
-------------------
majorID    | bigint (primary key)
-------------------

table 'B':
-------------------
majorID    | bigint (foreign key to table 'A's majorID)
minorID    | bigint (primary key)
totalSize  | bigint
-------------------

table 'C':
-------------------
objectID   | bigint (primary key)
majorID    | bigint (foreign key to table 'A's majorID)
minorID    | bigint (foreign key to table 'B's minorID)
startPoint | bigint
length     | bigint
-------------------

我想要做的是获取表“B”中所有行的列表，但显示每行剩余多少空间。

剩余空间可以通过找到最高的“startPoint”，将包含最高“startPoint”的行的“length”列的值相加，然后从表中的“totalSize”列中减去该组合值来找到' B'

我目前能够使用以下代码实现此目的:

create table #results (MinorID bigint, MajorID bigint, RemainingSpace bigint)

DECLARE @MinorID bigint
DECLARE @TotalSpace bigint
DECLARE @MajorID bigint
DECLARE cur CURSOR FOR
SELECT MinorID, MajorID, TotalSize FROM B
OPEN cur


FETCH NEXT FROM cur INTO @MinorID,@MajorID, @TotalSpace

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @UsedSize bigint

    SELECT TOP 1 @UsedSize = StartPoint + [length] FROM C
    WHERE MinorID = @MinorID AND MajorID = @MajorID
    ORDER BY StartPoint DESC

    INSERT INTO #results VALUES (@MinorID,@MajorID,@TotalSpace - @UsedSize)

    FETCH NEXT FROM cur INTO @MinorID,@MajorID, @TotalSpace
END

CLOSE cur
DEALLOCATE cur

SELECT * FROM #results
drop table #results       

问题是我预计这些表会变得非常大，而且我意识到在表上运行游标可能不是实现我想要的最快方法。

但是，我正在努力寻找更好的解决方案(星期一早上忧郁)，并希望比我更清醒/更擅长 SQL 的人可以提出解决方案!

注意:表格设计并非“一成不变”，因此如果唯一的解决方案是对数据进行非规范化，以便表格“B”保留其“占用空间”的记录，那么我愿意到那...

编辑:

我使用了已接受答案的修改版本，如下所示:

SELECT B.*, coalesce(C.StartPoint + C.Length,0) AS UsedSize
    FROM TableB B
    LEFT JOIN 
    (
      SELECT *, DENSE_RANK() OVER(PARTITION BY C.MajorID, C.MinorID ORDER BY C.StartPoint DESC) AS Rank
      FROM TableC C
    ) C
    ON C.MajorID = B.MajorID
    AND C.MinorID = B.MinorID
    AND C.Rank = 1

Answer 1

也许你可以使用 DENSE_RANK .

在此查询中，我将表 C 与额外列 Rank 连接起来。如果此列是最高起点，则此列的值为 1。在 (AND C.Rank = 1) 中，我们只提取该行。

SELECT B.*, (C.StartPoint + C.Length) AS UsedSize
FROM TableB B
INNER JOIN 
(
  SELECT *, DENSE_RANK() OVER(PARTITION BY C.MajorID, C.MinorID ORDER BY C.StartPoint DESC) AS Rank
  FROM TableC C
) C
ON C.MajorID = B.MajorID
AND C.MinorID = B.MinorID
AND C.Rank = 1