An ingenious solution to the continuous number segment of SQL query _mssql
Source: Internet
Author: User
There is a very ingenious SQL technique on the itpub to learn and record here.
The initial problem is this:
I have a table structure,
Fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125
(The second field may be contiguous data, and a breakpoint may exist.) )
How to query out such a result, query a continuous record.
Like the following?
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125
The friend on the itpub gave a very ingenious answer:
SELECT B.fphm, MIN (B.KSHM) Start_hm, MAX (B.KSHM) end_hm
From (SELECT a.*, To_number (a.kshm-rownum) cc
From (SELECT *
From T
Order by FPHM, KSHM) a
) b
GROUP by B.FPHM, b.cc
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
