Oracle相关查询语句整理汇总

查找表中某个字段重复值:

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)