create table TEST_01
(
A1 VARCHAR2(20),
A2 VARCHAR2(20),
A3 VARCHAR2(20),
A4 DATE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
select *from test_01
select count(*) ee from test_01
delete from test_01
insert into test_01 values('001','1','250', to_date ( '20171220' , 'YYYYMMDD'));
insert into test_01 values('001','1','250', to_date ( '20171222' , 'YYYYMMDD'));
insert into test_01 values('001','1','230', to_date ( '20061220' , 'YYYYMMDD'));
insert into test_01 values('001','0','250', to_date ( '20160920' , 'YYYYMMDD'));
insert into test_01 values('001','0','250', to_date ( '20091220' , 'YYYYMMDD'));
insert into test_01 values('002','1','244', to_date ( '20091120' , 'YYYYMMDD'));
insert into test_01 values('002','1','250', to_date ( '20171220' , 'YYYYMMDD'));
insert into test_01 values('002','1','250', to_date ( '20171227' , 'YYYYMMDD'));
insert into test_01 values('002','0','250', to_date ( '20200920' , 'YYYYMMDD'));
insert into test_01 values('002','0','250', to_date ( '20091220' , 'YYYYMMDD'));
insert into test_01 values('003','1','250', to_date ( '20191120' , 'YYYYMMDD'));
insert into test_01 values('003','1','250', to_date ( '20291220' , 'YYYYMMDD'));
insert into test_01 values('003','1','290', to_date ( '20171220' , 'YYYYMMDD'));
insert into test_01 values('003','0','250', to_date ( '20200920' , 'YYYYMMDD'));
insert into test_01 values('003','0','250', to_date ( '20091220' , 'YYYYMMDD'));
将A2='1'并且A3='250'并且A4时间最晚的挑出来,然后将这些以外的A2更新为0
select A1,max(A4) from test_01 where A2='1'and A3='250' group by A1
# 第一种方法
update test_01 t01
set t01.a2 = '0'
where t01.a2 = '1'
and t01.a3 = '250'
and t01.a1 || t01.a4 not in (
select A1 || max(A4)
from test_01
where A2 = '1'
and A3 = '250'
group by A1)
# 第二种方法(效率低,not in
后面数据在内存里,没有主键,查询时需要逐条查询)
update test_01 t01
set t01.a2 = '0'
where t01.a2 = '1'
and t01.a3 = '250'
and t01.a1 not in (select t11.a1
from test_01 t11
join (select t.A1 || max(t.A4) as c
from test_01 t
where t.A2 = '1'
and t.A3 = '250'
group by t.A1) t12
on t11.a1 || t11.a4 = t12.c)
# 第二种方法 (效率高,当not in 后面的数据在内存中,但重新加了主键(即有了索引,查询速度很快)),这时不是
逐条的查找,而是二分查找,提高的效率
update test_01 t01
set t01.a2 = '0'
where t01.a2 = '1'
and t01.a3 = '250'
and t01.a1||t01.a4 not in (select t11.a1||t11.a4
from test_01 t11
join (select t.A1 || max(t.A4) as c
from test_01 t
where t.A2 = '1'
and t.A3 = '250'
group by t.A1) t12
on t11.a1 || t11.a4 = t12.c)
# ||为字符串连接符,
select rownum, A1, c, '这是第' || rownum || '条'
from (select t.a1, max(t.a4) as c
from test_01 t
where t.A2 = '1'
and t.a3 = '250'
group by t.a1
order by t.a1) t1
#导出表格文件,求出里面的列和字段
SELECT T1.COLUMN_ID,
T1.COLUMN_NAME,
T2.COMMENTS,
DECODE(T1.DATA_SCALE,
NULL,
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')',
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ',' || ',' || T1.DATA_SCALE || ')'),
DECODE(t1.NULLABLE,'Y','N','N'),
t1.NULLABLE,
t1.TABLE_NAME
FROM USER_TAB_COLUMNS T1
LEFT JOIN USER_COL_COMMENTS T2
ON T1.COLUMN_NAME = T2.COLUMN_NAME
AND T1.TABLE_NAME = T2.TABLE_NAME
ORDER BY t1.TABLE_NAME,t1.COLUMN_ID
SELECT T1.COLUMN_ID,
T1.COLUMN_NAME,
T2.COMMENTS,
DECODE(T1.DATA_SCALE,
NULL,
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')',
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ',' || ',' || T1.DATA_SCALE || ')'),
DECODE(t1.NULLABLE,'Y','N','N'),
t1.NULLABLE,
t1.TABLE_NAME
FROM USER_TAB_COLUMNS T1
LEFT JOIN USER_COL_COMMENTS T2
ON T1.COLUMN_NAME = T2.COLUMN_NAME
AND T1.TABLE_NAME = T2.TABLE_NAME
ORDER BY t1.TABLE_NAME,t1.COLUMN_ID
SELECT T1.COLUMN_ID,
T1.COLUMN_NAME,
T1.table_name,
t1.COLUMN_ID,
T1.DATA_SCALE,
T1.DATA_TYPE,
T1.DATA_LENGTH,
t1.NULLABLE
FROM USER_TAB_COLUMNS T1 order by t1.TABLE_NAME,t1.COLUMN_ID
select *from USER_TAB_COLUMNS
select *from USER_COL_COMMENTS