Oracle-SQL skills (I) SQL case test of continuous record Query

Source: Internet
Author: User

Requirements:
You need to find that a customer has continuously handled the business for those days of a year.

The SQL implementation is as follows::
Create a table:
Copy codeThe Code is as follows:
Create table test_num
(Tyear number,
Tdate );

Test Data:
Insert into test_num
Select 2014, trunc (sysdate)-1 from dual union all
Select 2014, trunc (sysdate)-002 from dual union all
Select 2014, trunc (sysdate)-003 from dual union all
Select 2014, trunc (sysdate)-004 from dual union all
Select 2014, trunc (sysdate)-005 from dual union all
Select 2014, trunc (sysdate)-007 from dual union all
Select 2014, trunc (sysdate)-008 from dual union all
Select 2014, trunc (sysdate)-009 from dual union all
Select 2013, trunc (sysdate)-120 from dual union all
Select 2013, trunc (sysdate)-121 from dual union all
Select 2013, trunc (sysdate)-122 from dual union all
Select 2013, trunc (sysdate)-124 from dual union all
Select 2013, trunc (sysdate)-125 from dual union all
Select 2013, trunc (sysdate)-127 from dual union all
Select 2015, trunc (sysdate)-099 from dual union all
Select 2015, trunc (sysdate)-100 from dual union all
Select 2015, trunc (sysdate)-101 from dual union all
Select 2015, trunc (sysdate)-102 from dual union all
Select 2015, trunc (sysdate)-104 from dual union all
Select 2015, trunc (sysdate)-105 from dual;

Write SQL:
Copy codeThe Code is as follows:
Select tyear, MIN (TDATE) as startdate, MAX (TDATE), COUNT (TYEAR) AS ENDNUM
FROM (select a. *, A. TDATE-ROWNUM AS GNUM
FROM (SELECT * FROM TEST_NUM order by tyear, TDATE))
Group by tyear, GNUM
Order by tyear, MIN (TDATE)

Related Article

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.