oracle常用SQL语句

+ ",(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
"

——根据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; --控制只输出最新一条记录

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

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;

----查看表空间
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;

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

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")=(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;

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

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;