SQL Server card number segment grouping

Before to an e-commerce company in Shanghai to do a sell card system, encountered the problem of card number segment grouping. At first there is no good way to achieve, so in the blog park for help but not the result, unable to study SQL, finally fix.

Problem Description :

There is a card inventory table, there is a card number field, assuming the data: 16001,16002,16003,16008.16009,16010,16211,16212. Note: The actual data volume will be very large, the card
The number will be very long. Now to use SQL statistics, or program implementation, card segment number: 16001~16003 Number: 3 Card segment Number: 16008~16010 Number: 3
Kard: Number of 16211~16212:2

Requirements Description:

Table Inventory Table: Card name, denomination, card number, status, etc...

For example card name "Shanghai Jewelry Card" Denomination 200 of this card has many, each card has a corresponding card number (such as 557105041502662, may also have letters in front of the card number), these cards may be a batch of cards, denominations are the same, assuming that there are 10000, In the database, a card number is a record, but the customer does not want to see the list is a card, but the card number segment statistics, such as the number of 557105041502662~557105041502761 is 100. As long as the number of the card number is continuous, a record is formed to show the card number segment and number.

Actual Design: Table Cardstock:cardid (card type), Indetailid (ID of inbound order), Cardnum (card number) ...

SQL to resolve:

1 SelectCardid,indetailid, ( Case  when sum(1)>1  Then cast(MIN(Cardnum) as varchar( +))+ '~'+ cast(MAX(Cardnum) as varchar( +))Else cast(MIN(Cardnum) as varchar( +))End) asCardnumsection,sum(1) asCardcount, from(SELECTCardId, Indetailid, Cardnum, Cardnum-Row_number () Over(Partition byCardId, IndetailidORDER  byCardnum) asGrp fromcardstock) v

View Code

One of the cardnumsection is the card number segment, if a card directly shows the number on the line, the perfect solution

SQL Server card number segment grouping