在SQL Server中检索已排序的列值 [英] Retrieve sorted column value in SQL server
问题描述
我有什么:
我有一个专栏
What i have:
I have a Column
ID SerialNo
1 101
2 102
3 103
4 104
5 105
6 116
7 117
8 115
9 119
10 120
这些只是10个虚拟行,表中的实际行数为10万。
我想得到的:
像任何排序技术一样的方法或公式可以让我回报每个子系列的[SerialNo]列的起始和结束元素。例如,
预期成果:101-105,115-120
上述结果中的逗号分隔并不重要,只有起始和结束元素很重要。
我尝试过:
我是通过PL / SQL编程完成的,通过运行一个循环,我将得到的起始和结束元素存储在一个TABLE中。
但由于非常庞大没有。行(以十万分之一为单位)查询执行需要很长时间(大约2分钟)。
我还搜索了SQL Server的一些排序技术但我一无所获。因为渲染每一行将花费两倍的时间然后排序算法
These are just the 10 dummy rows actual rows in table are in lakhs.
What i Want to get:
A method or formula like any sorting technique which could return me the starting and ending element of [SerialNo] Column for every sub-series. For example,
Expected Result: 101-105, 115-120
The comma separation in the above result is not important, only the starting and ending elements are important.
What I have tried:
I did it by PL/SQL programming, by running a loop in which i'm getting the starting and ending elements getting stored in a TABLE.
But due to very huge no. of rows(in lakhs) the query execution is taking very long(around 2 minuts).
I have also searched about some sorting techniques for the SQL Server but i found nothing. Because rendering every row will take twice the time then a sorting algorithm
推荐答案
您正在寻找的是:gap和岛屿问题。
请参阅:
序列中的间隙和岛屿的SQL [ ^ ]
连续数字中的岛屿和差距 [ ^ ]
测试:
What you're looking for is called: gaps and islands issue.
See:
The SQL of Gaps and Islands in Sequences[^]
Islands and Gaps in Sequential Numbers[^]
Test it:
DECLARE @tmp TABLE(ID INT IDENTITY(1,1), SerialNo INT)
INSERT INTO @tmp (SerialNo)
VALUES(101),(102),(103),(104),(105),
(116),(117),(115),(119),(120)
SELECT E.SerialNo- E.RowNo AS Grp, MIN(E.SerialNo) AS GrpStartsAt , MAX(E.SerialNo) AS GrpEndsAt
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY SerialNo) AS RowNo, SerialNo
FROM @tmp
) AS E
GROUP BY E.SerialNo- E.RowNo
结果:
GRP 次> | GrpStartsAt 次> | GrpEndsAt 次> |
---|---|---|
100 | 101 | < TD> 105|
109 | 115 | 117 |
110 | 119 | 120 |
对于这么小的数据的一部分,它应该像魅力一样工作。
基本的想法是返回
SerialNo
按升序排序,然后使用 RowNo
与 SerialNo
一起创建 Grp
。
Result:
Grp | GrpStartsAt | GrpEndsAt |
---|---|---|
100 | 101 | 105 |
109 | 115 | 117 |
110 | 119 | 120 |
For such small portion of data, it should work like a charm.
The basic idea is to return
SerialNo
sorted in ascending order, then to use RowNo
together with SerialNo
to create Grp
.
这篇关于在SQL Server中检索已排序的列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!