Gordon Linoff
2019-07-03 22:07
这是一个间隙和孤岛的问题，但是我将使用行数差异方法：
select orderid, count(*), min(serialNumber), max(serialNumber), type
from (select t.*,
row_number() over (partition by orderid order by serialnumber) as seqnum,
row_number() over (partition by orderid, type order by serialnumber) as seqnum_type
from t
) t
group by orderid, type, (seqnum - seqnum_type)
order by orderid, min(serialNumber);
您serialnumber看起来像一个字符串，但它具有数字值。如果您可以信任它的排序，那么您甚至都不需要两个行号值：
select orderid, count(*), min(serialNumber), max(serialNumber), type
from (select t.*,
row_number() over (partition by orderid, type order by serialnumber) as seqnum_type
from t
) t
group by orderid, type, (serialnumber - seqnum_type)
order by orderid, min(serialNumber);
起作用的原因有点难以解释，但是如果运行子查询，则很明显。您将看到行号之间的差异如何标识您要标识的组。
相关问题
热门github
2
8
10
12
15
Linux, Jenkins, AWS, SRE, Prometheus, Docker, Python, Ansible, Git, Kubernetes, Terraform, OpenStack, SQL, NoSQL, Azure, GCP, DNS, Elastic, Network, Virtualization. DevOps Interview Questions
(翻译：包含Linux、Jenkins、AWS、SRE、Prometheus、Docker、Python、Ansible、Git、Kubernetes、Terraform、OpenStack、SQL、NoSQL、Azure、GCP、DNS、弹性、网络、虚拟化等DevOps 面试问题)
清洁得多...我只是将Flag方法卡在了我的小脑袋+1中