大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说oracle常用sql语句大全_oracle基本查询语句,希望您对编程的造诣更进一步.
+ ",(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
"代码100分
2.根据表的某个字段查询出最新的一条单据
代码100分——根据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.删除指定的表，模糊查询
代码100分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;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8604.html
