Oracle相关查询语句整理汇总

原创已于 2023-05-11 15:29:54 修改 · 1.4k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2020-09-07 08:35:37 首次发布

笔记专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了Oracle数据库中的SQL技巧，包括查找并处理重复数据、去除字段空格、解决表空间满的问题以及复杂的消费数据统计。通过具体示例展示了如何使用SQL进行数据清洗、表空间扩展和数据统计分析。

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

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)