使用相似属性对序列号进行分组 [英] Grouping Sequential Numbers with like attributes
问题描述
我发现了一篇与我试图做的文章有点相似的文章。这篇文章的标题是：
SQL查询 - 查找序列号块
这是文章
http://bytes.com/topic/sql-server/an...ential-numbers
我所拥有的略有不同。我正在尝试创建一个电话表报告，其中列出了客户的所有给定电话号码。有时，客户将拥有数百个序列号的块，这些序列都是相同的（数字本身除外）。还有其他字段，如description，type和LDCarrier。我需要的是列出所有的电话号码和附加信息（描述，类型，LDCarrier），但将所有顺序的数字分组，并具有相同的描述，类型和LDCarrier。如果我们有一个列出所有2500个电话号码的电话表，您可以想象列出所有这些号码的时间长短......我想将所有具有相同类型，描述和LDCarrier的序列号分组。 />
在一种情况下，有一组100个电话号码（例如 - 3098699700到3098699799），它们具有相同的描述，类型和LDCarrier。但是，3098699777具有不同的类型值。我需要3098699700到3098699776分组，3098699777全部自己和3098699778到3098699799所有分组。
任何不连续的数字和其他字段不同仍然会被列出，但他们将全部放在单独的行而不是分组。
从下面的示例查询（从我上面提到的问题），此查询将对所有序号进行分组，但它是无法将数字与具有相同值的其他字段组合在一起。我试图在那之后模拟我的查询，我只是无法做到。以下是另一个问题中使用的查询：
I found an article that was somewhat like what I was trying to do. The article was titled:
SQL Query - Find block of sequential numbers
Here is the article
http://bytes.com/topic/sql-server/an...ential-numbers
What I have is slightly different. I am trying to create a ''phone sheet'' report which lists all the given phone numbers for a customer. Sometimes, the customer will have blocks of hundreds of sequential numbers that are all the same (except for the number itself). There are other fields like description, type, and LDCarrier. What I need is to list all of the phone numbers and additional info (description, type, LDCarrier) but group all of the numbers that are sequential and have the same description, type, and LDCarrier. If we have a phone sheet that lists all 2500 phone numbers, you can imagine how long listing all of those numbers would be... I would like to group all sequential numbers who have all the same type, description, and LDCarrier.
In one case, there is a group of 100 phone numbers (example - 3098699700 through 3098699799) which have all the same description, type, and LDCarrier. However, 3098699777 has a different ''type'' value. I would need 3098699700 through 3098699776 grouped, 3098699777 all by itself and 3098699778 through 3098699799 all grouped.
Any numbers that are not sequential and the other fields are different would still be listed, but they would all be on separate lines instead of being grouped.
From the example query below (from the question I am referring to above), this query will group all sequential numbers, but it is not able to group numbers with other fields that have the same values. I am trying to model my query after that one and I am just not able to do it. Here is the query used in the other question:
推荐答案
当你说..
When you say..
我需要3098699700到3098699776分组，3098699777全部由自己和3098699778到3098699799全部分组。
I would need 3098699700 through 3098699776 grouped, 3098699777 all by itself and 3098699778 through 3098699799 all grouped.
你的意思是你只需要一个将它们组合在一起的标识符吗？或者你想要它在一排？
~~ CK
Do you mean you just need an identifier that group them all together? Or you want it to be in a single row?
~~ CK
感谢您的帮助。
这个例子对我（这个部分有用）的作用是它为每个组提供一行，从first_consecutive开始，然后是最后连续，然后是长度。
在给出的示例中，它将返回以下内容：
first_consecutive最后一个连续长度类型
3098699700 3098699776 77 ThisType
3098699777 3098699777 01 ThatType
3098699778 3098699799 22 ThisType
我希望这会有所帮助。
谢谢！
Thanks for helping.
What the example does for me (and that part works) is that it gives me a row for each group, starting with first_consecutive and then last consecutive and then the length.
In the example given it would return the following:
first_consecutive Last consecutive length type
3098699700 3098699776 77 ThisType
3098699777 3098699777 01 ThatType
3098699778 3098699799 22 ThisType
I hope this helps.
Thanks!
如果序列上有差距但是它们的类型相同会怎么样？
~~ CK
What would happen if there''s a gap on the sequence but they have the same type?
~~ CK
这篇关于使用相似属性对序列号进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
