Oracle day13

/*CREATE TABLE f726(
ID number,
Type VARCHAR2(20),
MasterID number,
Amount number
);
INSERT INTO f726 VALUES (1,'直接合同',NULL,5000);
INSERT INTO f726 VALUES (2,'补充合同',1,1000);
INSERT INTO f726 VALUES (3,'补充合同',1,500);
INSERT INTO f726 VALUES (4,'直接合同',NULL,6000);
INSERT INTO f726 VALUES (5,'直接合同',NULL,4000);
INSERT INTO f726 VALUES (6,'补充合同',5,1000);*/
select * from f726;
-------一开始
select * from(
select case when f1.type = '直接合同' then f1.id end id,
case when f1.type = '直接合同' then f1.type end type ,
case when f1.type = '直接合同' then f1.amount end amount,
  nvl(sum(f2.amount),0) amount2
 from f726 f1 left join f726 f2 on f1.id = f2.masterid
group by f1.id,f1.type,f1.amount order by f1.id) where id is not null;

-------最优解

select f1.id,f1.type,f1.amount,nvl(sum(f2.amount),0) amount2 from 
f726 f1 left join f726 f2 on f1.id = f2.masterid
 where f1.type = '直接合同'
group by f1.id,f1.type,f1.amount order by f1.id;

select distinct f1.id,f1.type,f1.amount,
nvl(sum(f2.amount)over(partition by f1.id),0) amount2 from 
f726 f1 left join f726 f2 on f1.id = f2.masterid
 where f1.type = '直接合同' order by f1.id;
 
-------XLJ
select id,type,amount,nvl(sum(amount2),0) from
(select f1.id,f1.type,f1.amount,f2.amount amount2 from
(select * from f726 where type='直接合同') f1
left join
(select * from f726 where type='补充合同') f2
on f1.id=f2.masterid)
group by id,amount,type
order by id;

-------WGD
select distinct a.id,a.type,a.amount,
sum(b.amount)over(partition by a.id)
 from (
select id,type,amount from f726 where type='直接合同') a left join
(select masterid,id,amount from f726 
where masterid is not null group by masterid,id,type,amount) b
on a.id=b.masterid order by a.id
------------------------------------------------------------------------

-- 查询当前用户默认表空间：
-- USERS是数据库默认的永久表空间
select default_tablespace from user_users;

-- 查看表空间信息
select * from v$tablespace;

-- 创建永久表空间
create tablespace test1
datafile 'D:\Oracle\oradata\orcl\t1.DBF'
size 1M autoextend on next 100K maxsize unlimited;

-- 创建临时表空间
create temporary tablespace test2
tempfile 'D:\Oracle\oradata\orcl\temp2.DBF'
size 100M autoextend on next 100K maxsize unlimited;

-- 删除表空间
drop  tablespace test2 including contents and datafiles;

------------------------------------------------------------------------

/*
create table g0731(
id number,
company varchar2(10),
salary number
);
INSERT INTO G0731 VALUES(1,'A',8341);
INSERT INTO G0731 VALUES(2,'A',9410);
INSERT INTO G0731 VALUES(3,'A',10050);
INSERT INTO G0731 VALUES(4,'A',15314);
INSERT INTO G0731 VALUES(5,'A',8451);
INSERT INTO G0731 VALUES(6,'A',9513);
INSERT INTO G0731 VALUES(7,'B',10005);
INSERT INTO G0731 VALUES(8,'B',13000);
INSERT INTO G0731 VALUES(9,'B',11540);
INSERT INTO G0731 VALUES(10,'B',10345);
INSERT INTO G0731 VALUES(11,'B',12210);
INSERT INTO G0731 VALUES(12,'B',9234);
INSERT INTO G0731 VALUES(13,'C',12000);
INSERT INTO G0731 VALUES(14,'C',8900);
INSERT INTO G0731 VALUES(15,'C',9000);
INSERT INTO G0731 VALUES(16,'C',10100);
INSERT INTO G0731 VALUES(17,'C',8000);*/
with t as
(select g.company,g.salary, 
row_number()over(partition by company order by salary ) r 
from g0731 g)
select t.company,t.salary from t where (t.company,t.r) in
(select t.company,ceil(max(t.r)/2) mid from t group by t.company)
;

-------LJQ
select g1.company,g1.salary from 
(select g.*,row_number()over(partition by company order by salary) r
from G0731 g) g1 join
(select company,count(*) a from G0731 group by company) g2
on g1.company=g2.company
where g1.r=round(g2.a/2);
------------------------------------------------------------------------

/*
create table g0817(id number,loadtime date);
insert into g0817 values(201,to_date('2021-01-28','yyyy-MM-dd'));
insert into g0817 values(201,to_date('2021-01-29','yyyy-MM-dd'));
insert into g0817 values(202,to_date('2021-01-29','yyyy-MM-dd'));
insert into g0817 values(202,to_date('2021-01-30','yyyy-MM-dd'));
insert into g0817 values(203,to_date('2021-01-30','yyyy-MM-dd'));
insert into g0817 values(201,to_date('2021-01-31','yyyy-MM-dd'));
insert into g0817 values(202,to_date('2021-01-31','yyyy-MM-dd'));
insert into g0817 values(201,to_date('2021-02-01','yyyy-MM-dd'));
insert into g0817 values(202,to_date('2021-02-01','yyyy-MM-dd'));
insert into g0817 values(201,to_date('2021-02-02','yyyy-MM-dd'));
insert into g0817 values(203,to_date('2021-02-02','yyyy-MM-dd'));
insert into g0817 values(203,to_date('2021-02-03','yyyy-MM-dd'));*/
select * from g0817;

select id,max(a) from
(select t.id,t.r,count(*) a from(
select g.*,(g.loadtime - row_number()over(partition by g.id order by g.loadtime)) r from g0817 g) t
group by t.id,t.r) group by id;

select id,max(times) from
(select a.id,count(*) times from
(with t as (
select g.*,row_number()over(partition by g.id order by g.loadtime) r
from g0817 g)
select id,loadtime,loadtime - r m from t ) a group by a.id,a.m ) group by id order by id;