Oracle SQL查询

嗨，汤姆，
我总是从您的文章和 @ asktom.oracle.com中学习新技巧，我的表格中有如下所示的数据。

create table tab1(dat date, price number)
/

insert into tab1 values (to_date('01-01-2020','MM-DD-YYYY'),1000)
/
insert into tab1 values (to_date('01-02-2020','MM-DD-YYYY'),1000)
/
insert into tab1 values (to_date('01-03-2020','MM-DD-YYYY'),2000)
/
insert into tab1 values (to_date('01-04-2020','MM-DD-YYYY'),2000)
/
insert into tab1 values (to_date('01-05-2020','MM-DD-YYYY'),3000)
/
insert into tab1 values (to_date('01-06-2020','MM-DD-YYYY'),2000)
/

I want to see result like below

Mindate      maxdate      Price
01/01/2020   01/02/2020   1000
01/03/2020   01/04/2020   2000
01/05/2020   01/05/2020   3000
01/06/2020   01/06/2020   2000
复制

您能帮我为上述结果形成一个SQL查询吗？

谢谢你的帮助，
女孩

你可以看到我是如何一点一点地建立起来的

SQL>
SQL> create table tab1(dat date, price number)
  2  /

Table created.

SQL>
SQL> insert into tab1 values (to_date('01-01-2020','MM-DD-YYYY'),1000)
  2  /

1 row created.

SQL> insert into tab1 values (to_date('01-02-2020','MM-DD-YYYY'),1000)
  2  /

1 row created.

SQL> insert into tab1 values (to_date('01-03-2020','MM-DD-YYYY'),2000)
  2  /

1 row created.

SQL> insert into tab1 values (to_date('01-04-2020','MM-DD-YYYY'),2000)
  2  /

1 row created.

SQL> insert into tab1 values (to_date('01-05-2020','MM-DD-YYYY'),3000)
  2  /

1 row created.

SQL> insert into tab1 values (to_date('01-06-2020','MM-DD-YYYY'),2000)
  2  /

1 row created.

SQL>
SQL> select
  2    tab1.*,
  3    case when nvl(lag(price) over ( order by dat),-1) != price then
  4      row_number() over ( order by dat )
  5    end tag
  6  from tab1
  7  /

DAT            PRICE        TAG
--------- ---------- ----------
01-JAN-20       1000          1
02-JAN-20       1000
03-JAN-20       2000          3
04-JAN-20       2000
05-JAN-20       3000          5
06-JAN-20       2000          6

6 rows selected.

SQL> select
  2    dat, price, max(tag) over ( order by dat ) as tag2
  3  from
  4  (
  5  select
  6    tab1.*,
  7    case when nvl(lag(price) over ( order by dat),-1) != price then
  8      row_number() over ( order by dat )
  9    end tag
 10  from tab1
 11  )
 12  /

DAT            PRICE       TAG2
--------- ---------- ----------
01-JAN-20       1000          1
02-JAN-20       1000          1
03-JAN-20       2000          3
04-JAN-20       2000          3
05-JAN-20       3000          5
06-JAN-20       2000          6

6 rows selected.

SQL> select tag2, min(dat), max(dat)
  2  from (
  3  select
  4    dat, price, max(tag) over ( order by dat ) as tag2
  5  from
  6  (
  7  select
  8    tab1.*,
  9    case when nvl(lag(price) over ( order by dat),-1) != price then
 10      row_number() over ( order by dat )
 11    end tag
 12  from tab1
 13  )
 14  )
 15  group by tag2
 16  order by 1;

      TAG2 MIN(DAT)  MAX(DAT)
---------- --------- ---------
         1 01-JAN-20 02-JAN-20
         3 03-JAN-20 04-JAN-20
         5 05-JAN-20 05-JAN-20
         6 06-JAN-20 06-JAN-20

SQL>
SQL>
复制