SQL表最高序列号 [英] SQL Table highest sequence number

问题描述

<块引用>

可能的重复:
通过嵌套连接选择最高的序列号

您好，我要写一个查询，我想为每个客户 ID 取最大的序列号(大序列号将根据最高的银行账户余额计算).

请考虑此表有 100000 条记录.

表格如下-

表格:

<前>**ClID** **SeqNu** **Bal**1 1 300001 2 267891 3 234561 4 123451 5 212342 3 124562 4 456322 1 234562 9 999993 4 123453 5 212343 3 124563 4 45632

结果是

<前>**ClID** **SeqNu** **Bal**1 1 300002 9 999993 4 45632

解决方案

实现此目的的最佳方式可能因您使用的 RDBMS 而异.如果您有窗口函数(例如 Oracle 9i+ 或 SQL Server 2012)，以下应该可以工作:

选择不同的ClId，first_value(SeqNu)结束(按 ClId 分区按 Bal desc 排序)作为 SeqNu，最大(巴尔)over(按 ClId 分区)作为 Bal从 your_table

Possible Duplicate:
Selecting the Highest Seq Number by nested Joining

Hello I have to write a query , I would like to take biggest sequence number for each client Id (big sequence number will be calculated based on highest bank account balance).

Please consider this table has 100000records.

table is like as below-

Table:

  
**ClID**      **SeqNu**     **Bal**  
1                 1              30000  
1                 2              26789  
1                 3              23456  
1                 4              12345  
1                 5              21234  
2                 3              12456  
2                 4              45632  
2                 1              23456  
2                 9              99999  
3                 4              12345  
3                 5              21234  
3                 3              12456  
3                 4              45632

Result would be

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

解决方案

The best way to accomplish this will probably vary depending on which RDBMS you're using. If you have windowing functions (Oracle 9i+ or SQL Server 2012, for instance), the following should work:

select distinct ClId, 
                first_value(SeqNu) 
                    over (partition by ClId 
                          order by Bal desc) as SeqNu, 
                max(Bal) 
                    over (partition by ClId) as Bal
from your_table

这篇关于SQL表最高序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！