查找表中某个字段重复值:
select 字段名,count(*) from table group by 字段名 having count(*) > 1
(1)例子:
select b.姓名,count(*) from XUESHENGYUE b group by b.姓名 having count(*) >1
(2)根据表中某个字段重复值,将多余的重复数据查出:
select * from id_areainfo a
where a.thirdareaid in (select b.thirdareaid from id_areainfo b group by b.thirdareaid having count
(b.thirdareaid) > 1)
oracle之批量去掉字段中的空格
select ltrim(col1) from t1;
--去掉字段值左边空格
update t1 set col1=ltrim(col1);
--去掉字段值右边空格
update t1 set col1=rtrim(col1);
--去掉字段值两边空格
update t1 set col1=trim(col1);
例子:
update shuikongyue a set a.姓名=trim(a.姓名)
关于“ORA-01653:表xxx无法通过8(在表空间SYSTEM)扩展
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;
–表空间满了,可以修改表空间,给表空间添加数据文件(给原有的表空间添加一个数据文件,让添加的数据文件与原有文件一起支持该表空间)
ALTER TABLESPACE "CCEN" ADD DATAFILE '/u01/oracledata/jndxecard/CCEN1.DBF' SIZE 20g AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
临时表空间增加temp:
SQL> create temporary tablespace temp2 tempfile ‘/opt/oracle/oradata/conner/temp1.dbf’ size 200M autoextend off;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp;
或者SQL> drop tablespace temp including contents and datafiles cascade constraints(彻底删除包括操作系统中的临时表空间的数据文件)
——统计消费明细
select c.dpname,b.outid,b.name,a.opfare,a.opdt from rec_main_consume a,base_customers b,base_custdept c
where a.opdt >= to_date('2016-11-01','yyyy-mm-dd')
and a.opdt < to_date('2016-11-11','yyyy-mm-dd')
and a.customerid=b.customerid
and b.custdept=c.dpcode
——按年月,以及部门分组消费汇总
select
to_char(a.opdt,'yyyy-mm')as "jiaoyiriqi",
b.dptname as "jiaoyibumen",
sum(a.opfare) as "jiaoyijine",
count(a.opcount) as "jiaoyicishu"
from rec_main_consume a,linshi_bumenrenyuan b
where a.opdt >= to_date('2016-01-01','yyyy-mm-dd')
and a.opdt < to_date('2021-01-01','yyyy-mm-dd')
and a.termid=b.termid
group by to_char(a.opdt,'yyyy-mm'),b.dptname
order by to_char(a.opdt,'yyyy-mm')
——按年月,以及部门分组圈存汇总
select
to_char(a.opdt,'yyyy-mm')as "chongzhiriqi",
sum(a.opfare) as "chongzhijine",
count(a.opcount) as "chongzhicishu"
from rec_subsidy_putout a
where a.opdt >= to_date('2016-01-01','yyyy-mm-dd')
and a.opdt < to_date('2021-01-01','yyyy-mm-dd')
group by to_char(a.opdt,'yyyy-mm')
order by to_char(a.opdt,'yyyy-mm')
——按日期汇总每天消费次数,消费金额汇总
—1.学生数据
select to_char(opdt,'yyyy-mm-dd') as "消费日期",
count(distinct(customerid)) as "每天消费人次",
count(*) as "每天消费笔数" ,
sum(a.opfare) as "每天消费金额"
from rec_main_consume a
where a.opdt >TO_DATE('2020-05-02','yyyy-mm-dd')
and a.opdt <TO_DATE('2020-05-03','yyyy-mm-dd')
and a.acccode=210
and a.customerid in (select customerid from base_customers where cardsfid in(4,6,9))
group by to_char(opdt,'yyyy-mm-dd')
order by to_char(opdt,'yyyy-mm-dd')
—2.教工数据
select to_char(opdt,'yyyy-mm-dd') as "消费日期",
count(distinct(customerid)) as "每天消费人次",
count(*) as "每天消费笔数" ,
sum(a.opfare) as "每天消费金额"
from rec_main_consume a
where a.opdt >TO_DATE('2020-05-02','yyyy-mm-dd')
and a.opdt <TO_DATE('2020-05-03','yyyy-mm-dd')
and a.acccode=210
and a.customerid in (select customerid from base_customers where cardsfid in(0))
group by to_char(opdt,'yyyy-mm-dd')
order by to_char(opdt,'yyyy-mm-dd')
—3.临时数据
select to_char(opdt,'yyyy-mm-dd') as "消费日期",
count(distinct(customerid)) as "每天消费人次",
count(*) as "每天消费笔数" ,
sum(a.opfare) as "每天消费金额"
from rec_main_consume a
where a.opdt >TO_DATE('2020-05-02','yyyy-mm-dd')
and a.opdt <TO_DATE('2020-05-03','yyyy-mm-dd')
and a.acccode=210
and a.customerid in (select customerid from base_customers where cardsfid in(1,2,3,5,7,8,10,11,12,13,14,15,16,17,18,19,20))
group by to_char(opdt,'yyyy-mm-dd')
order by to_char(opdt,'yyyy-mm-dd')
—4.总数据
select to_char(opdt,'yyyy-mm-dd') as "消费日期",
count(distinct(customerid)) as "每天消费人次",
count(*) as "每天消费笔数" ,
sum(a.opfare) as "每天消费金额"
from rec_main_consume a
where a.opdt >TO_DATE('2020-02-01','yyyy-mm-dd')
and a.opdt <TO_DATE('2020-02-02','yyyy-mm-dd')
and a.acccode=210
--and a.customerid in (select customerid from base_customers where cardsfid in(1,2,3,5,7,8,10,11,12,13,14,15,16,17,18,19,20))
group by to_char(opdt,'yyyy-mm-dd')
order by to_char(opdt,'yyyy-mm-dd')
MYSQL数据库查出重复数据,并且I保留ID是最大的
SELECT * FROM aiface_person WHERE outid IN (SELECT outid
FROM aiface_person
GROUP BY outid
HAVING COUNT(*) >1) AND id NOT IN (SELECT MAX(id) FROM aiface_person
GROUP BY outid HAVING COUNT(*)>1)