通过嵌套连接选择最高的序列号

我想为每个客户 ID 获取最大序列号（最大序列号将根据最高银行帐户余额计算）。

该表有100000条记录。

Tables

客户端序列表 T1

ClID              SeqId     
1                 11         
1                 12         
1                 13         
1                 14         
1                 15         
2                 16         
2                 17         
2                 18         
2                 19         
3                 110         
3                 111         
3                 112         
3                 113         

SeqBranch表T2

SeqId    BalID  
11         1  
12         2  
13         3  
14         4  
15         5  
16         6  
17         7  
18         8  
19         9  
110         10  
111         11  
112         12  
113         13

平衡表t3

BalID Balance  
1     30000  
2     26789  
3     23456  
4     12345  
5     21234  
6     12456  
7     45632  
8     23456  
9     99999  
10    12345  
11    21234  
12    12456  
13    45632

结果将是

ClID              SeqID          Balance  
1                 1              30000  
2                 9              99999  
3                 4              45632

我已经尝试过这种方式，但对我不起作用

SELECT  RS.Investigationid,MAX(stt.sequenceid) 'SeqId', T.HighestBalance 'Balance' 
FROM    ClientSeqTable  T1, SeqBranchTable T2, branbaltable t3,  
                ( SELECT    t1.clid ,MAX(T3.Balance) 'HighestBalance'
                    FROM    ClientSeqTable  T1, SeqBranchTable T2, branbaltable t3,
                    WHERE   t1.seqid =  T2.seqID
                    AND     T2. balId= T3. balId
                    GROUP BY  RS.Investigationid,stt.SequenceId
                    ) T
WHERE   T2.balId =  T3.BalId
AND T1.SeqId = T2.SeqId
AND     T3.HighestBalance = T2.balance
AND     T1.clID = t.ClID
GROUP BY  T1.ClID

通过以上查询结果如下。

ClID            SeqNu          Bal    
1               1              30000    
1               2              30000  
1               3              30000  
1               4              30000  
1               5              30000  
2               3              99999    
2               4              99999    
2               1              99999    
2               9              99999    
3               2              45632  
3               5              45632  
3               3              45632  
3               4              45632

如果您能够使用 row_number 函数，那么应该可以工作：

select 
    *
from 
(
    select 
        t1.ClID, t1.SeqId, t3.Bal, 
        RowNumber = row_number() over (PARTITION BY t1.ClID order by t3.bal desc)
    from 
        ClientSeqTable t1
    inner join
        SeqBranchTable t2 on t2.SeqId = t1.SeqId
    inner join
        Balancetable t3 on t3.BalID = t2.BalID
) t
where
    t.RowNumber = 1

重要的一点是行号按客户端 ID 分区，然后按余额降序排列。