oracle常用SQL语句

  • Z时代
  • 2024-01-10
  • 分类：综合

database

+ ",(select LISTAGG(mo.model_code,",") within group(order by mo.model_code) from srm_pos_resource_model mo where mo.supplier_id=t.supplier_id) modelCodeList
"

2.根据表的某个字段查询出最新的一条单据

 

——根据supplierId查询最新的一条单据
select * from (
select t.supplier_id,t.introduction_audit_id,t.creation_date,row_number()
over(partition by t.supplier_id order by t.creation_date desc) AS code_id
from srm_pos_introduction_audit t 
)
where code_id=1; --控制只输出最新一条记录

3.前30天的数据 

select * from 表 where 日期=to_date(to_char(sysdate-30,"yyyy/mm/dd"),"yyyy/mm/dd");

4.删除指定的表，模糊查询

 

declare
  v_sql varchar2(200);
  v_cnt number(10);
begin
  for v_tab in (select table_name from user_tables t where table_name like "Test%") loop
    v_sql := "drop table "|| v_tab.table_name;
    execute immediate v_sql ;
  end loop;
end;

5.创建表空空间

----查看表空间
select * from Dba_Tablespaces;
----创建表空间
create tablespace APPS_TS_TX_DATA logging datafile "D:/software/softwareWorkspace/professionalSoftwareWorkspace/Oracle/oraclexe/app/oracle/oradata/XE/APPS_TS_TX_DATA.dbf" 
 size 200m autoextend on next 100m maxsize 20480m extent management local;

6.

拆分以逗号分隔的字符串为多行

 

SELECT REGEXP_SUBSTR ("26238,26239,55198", "[^,]+", 1,rownum)
from dual connect by rownum<=LENGTH ("26238,26239,55198") - LENGTH (regexp_replace("26238,26239,55198", ",", ""))+1;

7、oracle的年份比较——去年年份

 

select t.supplier_id,t.supplier_type,t.creation_date from srm_pos_supplier_info t
where 1=1
and to_char(t.creation_date,"yyyy")<to_char(sysdate,"yyyy")
and to_char(t.creation_date,"yyyy")>=(SELECT to_char(sysdate,"yyyy")-1 FROM dual);
--取年份
Select to_number(to_char(sysdate,"yyyy")) from dual;
select extract (year from sysdate) from dual;
--取月份
select to_number(to_char(sysdate,"mm")) from dual;
select extract (month from sysdate) from dual;
--取日期
Select to_number(to_char(sysdate,"dd")) from dual;
select extract (day from sysdate) from dual;
--获取当前年月日：
Select to_char(sysdate,"yyyy") from dual;
Select to_char(sysdate,"MM") from dual;
Select to_char(sysdate,"dd") from dual;
Select to_char(sysdate,"yyyy-mm") from dual;
Select to_char(sysdate,"yyyy-MM-dd") from dual;
Select Extract(year from sysdate) from dual;
select extract (month from sysdate) from dual;
select extract (day from sysdate) from dual;

8.取两个日期中的最大日期

 

select decode(sign(t.last_update_date-sysdate),-1,sysdate,t.last_update_date) from srm_pos_scene_manage t;

9.取上一个月，去年的日期

 

select to_char(add_months(sysdate,-1), "yyyy-MM") from dual;
select to_char(add_months(sysdate,-1), "MM") from dual;
SELECT to_char(sysdate,"yyyy")-1 FROM dual;

 

 

以上是 oracle常用SQL语句 的全部内容， 来源链接： utcz.com/z/533500.html

回到顶部