oracle | sql server |
CREATE OR REPLACE PROCEDURE 存储过程名(参数列表) IS Begin ---存储过程内容 End 存储过程名; go --也可将多个存储过程统一放到一个package中,比如 create or replace package 包名 is type RtnCursor IS REF CURSOR ; --声明游标,如果包中的对象用不到游标,也可不声明 PROCEDURE 存储过程名(参数列表); end 包名; go create or replace package body 包名 is PROCEDURE 存储过程名(参数列表) is 局部变量列表; begin ---存储过程内容 end 存储过程名; end 包名; go | CREATE PROCEDURE 存储过程名 (参数列表) AS begin --存储过程内容 end go |
1、 指定参数类型,但不指定长度 2、 在参数及类型间要加入出标识(IN、OUT),其中,in可省略 比如: 参数1 IN VARCHAR2, 参数2 VARCHAR2, 参数2 OUT NUMBER | 1、 指定参数类型,并要指定长度 2、参数名以@作为开头 3、 对于出参需要在类型后面加OUTPUT(或OUT) 比如: @参数1 VARCHAR(10), @参数2 int, @参数3 int output |
1、直接使用存储过程名 2、参数在存储过程后面的括号包内列出 3、输出参数不需要使用out进行标识 比如: 存储过程名(参数1,参数2,参数3); --独立的存储过程 包名.存储过程名(参数1,参数2,参数3);--调用包中的存储过程 | 1、 调用存储过程名前面需要加exec关键字 2、 参数在存储过程后面逐个列出 3、 出参后面要加关键字output或out 比如: EXEC 存储过程名 参数1的值,参数2的值,@参数3变量 OUTPUT |
CREATE OR REPLACE FUNCTION 函数名 (参数列表) RETURN 返回值类型 IS BEGIN --函数体 return 返回值; END; --也可将函数放到package包中声明和定义 | CREATE FUNCTION 函数名 (参数列表) RETURNs 返回值类型 AS begin --函数内容 return 返回值 end |
1、指定参数类型,但不指定长度 2、在参数及类型间要加入出标识(IN、OUT),in可省略 比如: 参数1 IN VARCHAR2, 参数2 VARCHAR2, 参数3 OUT NUMBER 3、 支持对数据增、删、改操作 4、 支持动态SQL语句 | 1、指定参数类型,并要指定长度 2、不支持输出参数 比如: @参数名 VARCHAR(10) 3、 不支持对数据增、删、改操作 4、 不支持动态SQL语句 |
1、 直接使用函数名 比如: vResult :=函数名(参数1, 参数2,参数3); vResult :=包名.函数名(参数1, 参数2,参数3); | 1、在函数名前面加上架构名 比如: Set @result= lcm5719999.函数名(参数1的值, 参数2的值) |
1、游标声明: declare cursor 游标名 is select语句 2、使用游标过程: 打开(open 游标名;)->提取(fetch 游标名 into..)->关闭(close 游标;) 3、支持快捷使用游标,直接使用for循环,数据库会自动打开、提取及关闭游标 4、支持引用游标,即声明游标时不指定游标结构,创建游标变量需要两个步骤,声明ref游标类型,并声明ref游标类型的变量,在open游标时才指定游标对应的sql; 5、使用布尔型变量"游标名%found"或"游标名%nofound"来标识最近一次fetch是否成功,用"游标名%isopen"来标识游标是否已经打开; | 1、游标声明: declare 游标名 cursor for select语句 2、使用游标过程 打开(open 游标名)->提取(fetch next from 游标名)->关闭(close 游标名)->丢弃(deallocate 游标名) 3、使用int类型的全局变量@@fetch_status控制游标是否正常fetch,其值有以下三种,分别表示三种不同含义:0 fetch成功,-1 fetch 语句失败或此行不在结果集中,-2 被提取的行不存在 |
1、变量前不可加@符号; 2、存储过程中变量声明不需要declare; 3、变量类型可按表中字段类型动态定义; 比如: 变量1 varchar2(5); 变量2 表名.列名%type; | 1、变量前需要加@符号 2、变量声明需要使用Declare关键字 比如: DECLARE @变量1 varchar(5); 3、不支持按表字段类型动态定义 |
1、变量直接赋值, 变量 := 表达式; 比如: vResult := ‘abcd’; 2、通过SQL语句,Select 表达式 into 变量 from 表 比如: Select col1,col2 into v_col1,v_col2 from table1 Where id = 1000 ; --但当表中没有满足条件的数据时会报""no_data_found""的异常,当表中有多条满足条件的记录时,会报""too_many_rows""异常 3、通过游标, 比如: declare vCur is select col1,col2 from table1; begin open vCur; fetch vCur into v_col1,v_col2; close vCur; end; | 1、变量直接赋值,Set 变量=表达式; 比如: Set @result = ‘abcd’ 2、通过SQL语句赋值,Select 变量=表达式 from 表 比如: Select @col1=col1,@col2=col2 from table1 where id = 1000 --当表中没有满足条件的数据时变量值为null,当表中有多条满足条件的记录时,变量值为最后一条记录的值 |
在批查询中的各条SQL语句必须使用分号 ; 作为语句的结束 | SQL语句一般不强制要求加分号;作为结束符,但在批查询中,with子句的上一个一句必须要以;结束;批查询直接使用go进行分割 |
Oracle对字符区分大小写 | 默认对字符不区分大小写,也可修改数据库配置支持区分大小写 |
有序列sequence对象,无自动增长列 | 无序列对象,表中有自动增长列 |
alter table 表名 modify 列名 其他属性 | alter table 表名 alter column 列名 数据类型 |
建表sql中,字段的默认值要放在not null约束之前,格式为:列名 数据类型 默认值 not null,比如:create table tab1(a int default 0 not null) | 默认值和not null约束的位置不做限制,比如:create table tab1(a int default 0 not null) 和 create table tab2(a int not null default 0) 均能成功执行 |
1、结果集可做为表(子查询)使用,使用时可不加别名 比如: Select * from (select * from tab1) 2、给子查询提供别名时,在子查询后面直接给别名,不能使用as 比如: Select * from (select * from tab1) a --如果写成 as a则会报语法错误 3、虚表 dual的使用,对于select计算某些与实体表无关的表达式时,要使用虚表dual 比如: Select round(1/3,2) from dual ; | 1、结果集可做为表(子查询)使用,使用时必须加别名 比如: Select * from (select * from tab1) a 2、给子查询提供别名时,可使用 as 别名 的方式,其中as可省略 Select * from (select * from tab1) as a 3、对于Select计算某些与实体表无关的表达式时,可使用不带from的select语句 比如: Select round(1/3,2) ; |
1、不能使用update table1 set col1 = table2.col2 from table2 where ..的方式进行更新,应该使用 update table1 set col1 = (select table2.col2 from table2 where .. ) and exists(select 1 from table2 where ..)的语法,其中and exists子句是必须的,否则不满足条件的记录都会被更新成null 2、可给更新的目标表指定别名,比如 update table1 a set... | 1、可关联表更新,比如: Update a set a.value = isnull(b.value,' ') From b where a.id = b.id 2、不能给更新的目标表指定别名,比如 update table1 a set...会报语法错误 |
1、不可关联表删除,比如使用exists或in子句,比如: delete from a where exists (select b.id from b where a.id = b.id ) 2、可给删除的表指定别名,比如 delete from table1 a where 1= 0 | 1、可关联表删除,比如: delete a From a Inner join b on a.id = b.id 2、不可给删除的表指定别名,比如 delete from table1 a where 1= 0 会报语法错误 |
oracle 9及以后版本支持,语法: merge into 表名1/视图名 <别名1> using 表名2/(子查询 ) <别名2> on (关联条件) when matched then update set 列1 = default/表达式/别名2中的列,.... <where ..> delete <where ..> when not matched then insert (column_list)values(values_list) <where ..> | sql server2008及以后版本才支持merge,语法: merge into <target_table> as 别名 using <table_source> on <关联条件> when matched and <条件1> then UPDATE SET ... 或 DELETE when not matched by target and <条件2> then INSERT ( column_list ) VALUES ( values_list ) when not matched by source and <条件3> then UPDATE SET ... 或 DELETE ; |
with字句后只能跟select,比如 create table hptest (a int ,b int); --查询 with t as (select 1 a,2 b ) select a,b from t ; --插入 with t as (select 1 a,2 b ) insert into hptest(a,b) select a,b from t ; --更新 with t as (select 1 a1,2 b1 ) update hptest set a = t.a1 from t; --删除 with t as (select 1 a,2 b from hptest) delete from t ; | with子句后可跟delete\insert\update\merge等,比如create table hptest (a int ,b int); --插入 with t as (select 1 a,2 b ) insert into hptest(a,b) select a,b from t ; --更新 with t as (select 1 a,2 b ) update hptest set a = t.a from t; --合并 with t as (select 1 a,2 b ) merge into hptest using t as f on ( hptest.b = f.b ) when matched then update set hptest.a = f.a; |
over子句可以在单个查询中将多个开窗函数与单个 FROM 子句一起使用,语法: over( [partition by ...] [order by ... ] [rows/range between ... and ...] ) 比如:create table hptestover1 ( v varchar(10), v1 int, rn int ); insert into hptestover1(v,v1,rn) select 'a',level,level from dual connect by level < 7; insert into hptestover1(v,v1,rn) select 'b',level*level,level from dual connect by level < 4; insert into hptestover1(v,v1,rn)values('c',1,5); insert into hptestover1(v,v1,rn)values('c',3,6); insert into hptestover1(v,v1,rn)values('c',9,10); insert into hptestover1(v,v1,rn)values('d',1,2); insert into hptestover1(v,v1,rn)values('d',2,4); insert into hptestover1(v,v1,rn)values('d',4,7); --RANGE只指定了前后两个值之间相差值的范围,而ROWS则指定了前后多少行的范围。 --进行查询分析 select v,v1,rn, sum(rn) over(partition by v order by v1 rows between 1 preceding and current row ) row_p1, --将本行和上一行的rn值相加 sum(rn) over(partition by v order by v1 range between 1 preceding and current row ) range_p1, --将本行之前的rn差额不超过1,本行之后的的rn等于当前行的所有rn值相加 sum(rn) over(partition by v order by v1 range between 2 preceding and current row ) range_p2, --将本行之前的rn差额不超过2,本行之后的的rn等于当前行的所有rn值相加 sum(rn) over(partition by v order by v1 rows 1 preceding) row_p1_1 , --将本行和上一行的rn值相加,省略了between,因此,oracle认为结束行时当前行,而开始行为1 precding sum(rn) over(partition by v order by v1 range 1 preceding) range_p1_1 , --将本行之前的rn差额不超过1,本行之后的的rn等于当前行的所有rn值相加,省略了between,因此,oracle认为结束行时当前行,而开始行为1 precding sum(rn) over(partition by v order by v1 range 2 preceding) range_p2_1 , --将本行之前的rn差额不超过2,本行之后的的rn等于当前行的所有rn值相加 sum(rn) over(partition by v order by v1 range 4 preceding) range_p4_1 , --将本行之前的rn差额不超过2,本行之后的的rn等于当前行的所有rn值相加 sum(rn) over(partition by v order by v1 rows between current row and 1 following) row_f1, --将本行和下一行的rn值相加 sum(rn) over(partition by v order by v1 range between current row and 1 following) range_f1, --将本行和下一行的rn值相加 sum(rn) over(partition by v order by v1 range between current row and 4 following) range_f4, --将本行和下一行的rn值相加 sum(rn) over(partition by v order by v1 rows between unbounded preceding and current row) row_pn, --将第一行到当前行的所有rn值相加 sum(rn) over(partition by v order by v1 range between unbounded preceding and current row) range_pn, --将第一行到当前行的所有rn值相加 sum(rn) over(partition by v order by v1 rows unbounded preceding ) row_pn_1, --将第一行到当前行的所有rn值相加,省略between sum(rn) over(partition by v order by v1 range unbounded preceding ) range_pn_1, --将第一行到当前行的所有rn值相加,省略between sum(rn) over(partition by v order by v1 rows between current row and unbounded following) row_fn, --将当前行到最后一行的所有rn值相加 sum(rn) over(partition by v order by v1 range between current row and unbounded following) range_fn, --将当前行到最后一行的所有rn值相加 sum(rn) over(partition by v order by v1 rows between 1 preceding and 1 following) row_p1_f1, --将当前行、前一行、最后一行的所有rn值相加 sum(rn) over(partition by v order by v1 range between 1 preceding and 1 following) range_p1_f1, --将当前行、前一行、最后一行的所有rn值相加 sum(rn) over(partition by v order by v1 rows between unbounded preceding and unbounded following) row_all, --将所有v值相同的行相加 sum(rn) over(partition by v order by v1 range between unbounded preceding and unbounded following) range_all, --将所有v值相同的行相加 sum(rn) over(partition by v ) s_all --将所有v值相同的行相加 from hptestover1 order by v,v1; --清理测试表 drop table hptestover1; | 语法和oracle基本相同,但其中的[rows/range between ... and ...]子句是sql server2012之后才支持的内容,2008及以前版本只支持: over( [partition by ...] [order by ... ] ) 比如: select row_number() over(partition by VendorAZCode order by CreateDate) as subrn, VendorAZCode,CreateDate,TotalTaxInValue AValue, /*本供应商orderby列表中与当前行值相同的所有行的分区*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate range current row) as VDValue, /*本供应商orderby列表中与第一行值相同的所有行的分区*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate range unbounded preceding) as VFDValue, /*当前供应商本笔业务之前的采购额*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate rows between unbounded preceding and 1 preceding) as VBFValue, /*当前供应商本笔业务及本笔业务之前的采购额*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate rows unbounded preceding) as VBFDValue, /*当前供应商本笔业务及本笔业务之前的采购额*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate rows 1 preceding) as VB1FDValue, /*当前供应商本笔业务之后的采购额*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate rows between 1 following and unbounded following) as VAFValue, /*当前供应商本笔业务及本笔业务之后的采购额*/ sum(TotalTaxInValue) over(partition by VendorAZCode order by CreateDate rows between current row and unbounded following) as VAFDValue, /*当前供应商总采购额*/ sum(TotalTaxInValue) over(partition by VendorAZCode) as VTValue, /*所有业务总额*/ sum(TotalTaxInValue) over() as TValue from PurchaseInvoices order by row_number() over(order by VendorAZCode,CreateDate) |
1、普通动态SQL语句 Execute immediate ‘update table1 set column1=5’; 用变量替换SQL语句 vSql := ‘update table1 set column1=5’ Execute immediate v_sql; 2、带出参动态SQL语句 vCount number(10); vSql varchar2(1000); vSql := 'select count(*) from table1'; execute immediate v_sql into vCount; 3、绑定变量 比如: v_sql := 'update table1 set col1 = :col1,col2 = :col2 where col3 = :col3' execute immediate v_sql using v_code, v_name,'1001' ; | 1、普通动态SQL语句 exec('update table1 set col1 = 5') 或 exec sp_executesql N'select * from table1' -- 字符串前一定要加N 用变量替换SQL语句 Declare @sql Nvarchar(1000) Set @sql='select * from table1' exec sp_executesql @sql 或 exec @sql 2、带输出参数的动态SQL语句 declare @count int declare @sqls nvarchar(4000) set @sqls='select @a=count(*) from table1 ' exec sp_executesql @sqls,N'@a int output',@count output 3、参数化的sql 比如: DECLARE @sql NVARCHAR(1000); SET @sql = 'update table1 set col1 = @col1,col2 = @col2 where col3 = @col3' EXEC sp_executesql @sql,N'@col1 varchar(20),@col2 varchar(20),@col3 varchar(20)',@col1, @col2,'1001' |
1、在Oracle中采用伪列rownum 获取结果集中排在前面的部分记录 比如: 返回结果集中前10条记录 : Select * from table1 where rownum <=10 --但Rownum可使用<、<=符号,不可使用>、>=符号,如果使用=号只可=1 2、Rownum列还常用于形成结果集的顺序号,从而可获取一定序号范围的行 比如: 获取按code排名第10到20行 Select * from (Select a.*,rownum as nrow from tab01 a order by code) where nrow between 10 and 20 3、oracle也可通过ROW_NUMBER()排名函数实现 比如: 获取按code排名第10到20行 SELECT a.* FROM (SELECT a.*,ROW_NUMBER() OVER( ORDER BY a.code ) AS nrow FROM table1 a) a where nrow between 10 and 20 | 1、SQLServer中采购top方式获取结果集排在前面的部分记录 比如:返回结果集中前10条记录 Select top (10) * from table1 2、sqlserver也可通过ROW_NUMBER()排名函数实现 比如: 获取按code排名第10到20行 SELECT a.* FROM (SELECT a.*,ROW_NUMBER() OVER( ORDER BY a.code ) AS nrow FROM table1 a) a where nrow between 10 and 20 |
IF 条件表达式 then {语句块} elsif 条件表达式 then Else {语句块} End if ; | if 条件表达式 begin {语句块} end else if 条件表达式 begin {语句块} end else begin {语句块} end --如果语句块中有多于1条SQL语句,必须要使用begin … end |
1、 表达式 Case 表达式 when 匹配表达式 then 结果表达式1 else 结果表达式2 end 比如: Select case name when ‘张三’ then 1 when ‘李四’ then 2 else 0 end From person 或 Case when 条件表达式 then 结果表达式1 else 结果表达式2 end 比如: Select case when name=‘张三’ then 1 when name=‘李四’ then 2 else 0 end From person case表达式可使用decode函数代替, decode的语法:decode(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,decode函数的结果返回then1,...,如果不等于任何一个if值,则返回else 2、 流控制语句 流控制与表达式结构很相似,只是结果表达式换成语句块 Case 表达式 when 匹配表达式 then {语句块1} else {语句块2} end case; 比如: case v_name when ‘张三’ then v_number := 1; when ‘李四’ then v_number:=2; else v_number:=0 end case; 或 Case when 条件表达式 then {语句块1} else {语句块2} end case; 比如: case when v_name = ‘张三’ then v_number := 1; when v_name = ‘李四’ then v_number:=2; else v_number:=0 end case; | 1、 case表达式用法同Oracle,但sql server2008及以前的版本不支持decode函数 2、不支持流控制 |
临时表结构需要象实表一样事先定义后再在SQL脚本中使用,使用方式和实表相同 临时表创建语句: create temporary table 临时表名 ( 字段列表 ) on commit delete/preserve rows --分别标识为"基于事务的临时表"和"基于会话的临时表" | 临时表是在执行数据库脚本会话过程中创建并使用,临时存储在tempdb数据库中,临时表名前面带有#号(或##全局临时表) 比如: create table #临时表名 ( 字段列表 ) 临时表也可在使用过程中自动创建 Select paraid,vvalue,flowid,id into #临时表名 from table1 |
1、 null值转换: 1)、nvl(c1,c2) 如果c1为null,则返回c2,否则返回c1 2)、nvl2(c1,c2,c3) 如果c1不为null则返回c2,如果c1为null则返回c3 例如:select nvl(null,'a'),nvl2('a', 'b', 'c') isnull,nvl2(null, 'b', 'c') isnotnull from dual; | 1、 isnull(c1,c2) 如果c1为null,则返回c2,否则返回c1 |
2、取系统日期: sysdate | 2、 getdate() |
3、 instr(字符表达式1,字符表达式2 [,pos][,nth]): 从“表达式1”的“pos”位置搜索第“nth”个“表达式2”的位置(若未找到则返回0) | 3、 CHARINDEX(字符表达式1,字符表达式2 [,pos]): 从“表达式2”的“pos”位置搜索“表达式1”并返回“表达式1”的起始位置(若未找到则返回0) |
4、类型转换函数: To_char(),to_date(),to_number(),cast(待转换的值 as 目标类型) | 4、convert(目标类型,待转换的值,格式),cast(待转换的值 as 目标类型) |
5、字符串截取:substr(源字符串,开始位置,截取长度) , 比如: substr('abcdef',1,2) --'ab' | 5、字符串截取: substring(源字符串,开始位置,截取长度), 比如: substring('abcdef',1,2) --'ab' |
6、字符串长度: length(字符串)/*返回字符串长度,统计了字符串末尾的空格*/,datalength(字符串)/*返回字符串所占字节数*/ | 6、字符串长度: len(字符串)/*返回字符串长度,sql server2005中,len统计了字符串末尾的空格,但是2008及后续版本不再统计字符串末尾的空格*/,datalength(字符串)/*返回字符串所占字节数*/ |
7、删除空格: trim(待处理字符串) /*去除两侧空格*/、ltrim(待处理字符串)/*去除左侧空格*/、rtrim(待处理字符串)/*去除右侧空格*/ | 7、ltrim(待处理字符串)/*去除左侧空格*/、rtrim(待处理字符串)/*去除右侧空格*/,无trim函数,可使用ltrim(rtrim(待处理字符串))变通实现 |
8、补位函数: 1)、LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点: 如果n<c1.length则从右到左截取指定长度返回; 如果n>c1.length and c2 is null,以空格从左向右补充字符长度至n并返回; 如果n>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回; 比如: SELECT LPAD('What is this',5),LPAD('What is this',25),LPAD('What is this',25,'-') FROM DUAL; 2)、RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与lpad相同,不过补充字符是从右向左方向,正好与lpad相反; 例如:SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL; | 8、sql server2008及以前版本没有补位函数,sql server 2012新增了lpad和rpad函数 |
9、日期函数: 1)、ADD_MONTHS() 返回指定日期月份+n之后的值,n可以为任何整数。 比如: SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL; 2)、CURRENT_DATE 返回当前session所在时区的默认时间 比如: SQL> alter session set nls_date_format = 'mm-dd-yyyy' ; SQL> select current_date from dual; 3)、SYSDATE 功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。 比如: SELECT SYSDATE,CURRENT_DATE FROM DUAL; 4)、LAST_DAY(d) 返回指定时间所在月的最后一天 比如: SELECT last_day(SYSDATE) FROM DUAL; 5)、NEXT_DAY(d,n) 返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。 比如: 三思用的中文nt,nls_language值为SIMPLIFIED CHINESE SELECT NEXT_DAY(SYSDATE,5) FROM DUAL; SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL; 两种方式都可以取到正确的返回,但是: SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL; 则会执行出错,提供你说周中的日无效。 6)、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0 比如: SELECT months_between(SYSDATE, sysdate), months_between(SYSDATE, add_months(sysdate, -1)), months_between(SYSDATE, add_months(sysdate, 1)) FROM DUAL; 7)、ROUND(d[,fmt]) 返回指定格式的日期,如果不指定fmt参数,则默认返回距离指定日期最近的日期。 比如: SELECT to_char(round(SYSDATE,'HH24'),'yyyy-MM-dd hh24:mi:ss') FROM DUAL; 8)、TRUNC(d[,fmt]) 对日期做截断处理 比如: SELECT to_char(trunc(SYSDATE,'HH24'),'yyyy-MM-dd hh24:mi:ss') FROM DUAL; | 9、日期函数:,比如:DECLARE @Date DATETIME = GETDATE(); select @Date AS '目前时间' ,DATEADD(DD,-1,@Date) AS '前一天' ,DATEADD(DD,1,@Date) AS '后一天' /*月计算*/ ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算,保证日恒久为1号 ,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天,建议使用DATEADD而不要直接“-1” ,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)' ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天' ,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天' ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天' ,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天' /*周计算*/ ,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关 ,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关 ,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推 ,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关 ,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关 ,DATENAME(WEEKDAY,@Date) AS '本日是周几' ,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日,2-星期一,3-星期二......7-星期六 /*年度计算*/ ,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初' ,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末' ,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初' ,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末' ,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初' ,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末' /*季度计算*/ ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初' ,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末' ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初' ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末' ,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初' ,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末' |
10、返回guid字符串:sys_guid() | 10、返回guid字符串:newid() |
11、返回ascii码对应的字符: chr(ascii码值) ,比如select chr(97),ascii('a') from dual | 11、返回ascii码对应的字符串: char(ascii码值),比如select char(97),ASCII('a') |
12、字符串聚合函数: oracle10版本可使用:wmsys.wm_concat()函数,但该函数未文档化,后续版本可能被弃用 oracle11g以后版本支持:LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )] 比如: --返回各部门人员列表 SELECT lszgzd_dwbh,lszgzd_bmbh, LISTAGG(lszgzd_zgxm, ',') WITHIN GROUP (ORDER BY lszgzd_zgbh) Employees, wmsys.wm_concat(lszgzd_zgxm) FROM lszgzd GROUP BY lszgzd_dwbh,lszgzd_bmbh ORDER BY lszgzd_dwbh,lszgzd_bmbh; | 12、字符串聚合:使用for xml path实现, 比如:--对字符串进行分组聚合 SELECT lszgzd_dwbh,lszgzd_bmbh , STUFF( (SELECT ','+ lszgzd_zgxm FROM lszgzd a WHERE a.lszgzd_dwbh = b.lszgzd_dwbh and a.lszgzd_bmbh = b.lszgzd_bmbh order by lszgzd_zgbh FOR XML PATH('') ),1 ,1, '' ) Employees FROM lszgzd b GROUP BY lszgzd_dwbh,lszgzd_bmbh ORDER BY lszgzd_dwbh,lszgzd_bmbh; --将满足条件的所以字符串相加 DECLARE @Chars VARCHAR(max) SELECT @Chars = ISNULL(@Chars + ', ' +lszgzd_zgxm, lszgzd_zgxm) FROM lszgzd order by lszgzd_zgbh SELECT @Chars --或者 select ',' + lszgzd_zgxm as [text()] from lszgzd order by lszgzd_zgbh for xml path('') --或者 select ',' + lszgzd_zgxm from lszgzd order by lszgzd_zgbh for xml path('') |
13、访问组内当前行之前或之后的行:lag\lead函数,Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。 这种操作可以代替表的自联接,并且LAG和LEAD有更高的效率。 语法: lag(exp_str,offset,defval) over() ; Lead(exp_str,offset,defval) over() ; 其中,exp_str为要取的列 ,offset取偏移后的第几行数据 ,defval:没有符合条件的记录时的默认值; 比如: create table hpa (a varchar(10),b varchar(10),c varchar(10),d varchar(10)); insert into hpa values('1','100','102','103'); insert into hpa values('2','200','202','203'); insert into hpa values('3','300','302','303'); insert into hpa values('4','400','402','403'); commit; select a,b,c,d,lag(d) over (order by d) lag,lead(d) over (order by d) lead from hpa; select a,b,c,d,lag(d,1,0) over (order by d) lag,lead(d,1,d) over (order by d) lead from hpa; | 13、访问组内当前行之前或之后的行:lag\lead函数,sql server2012以后才支持,语法同oracle |
字符相加 || :比如: ‘A’||’B’ = ‘AB’ | 字符相加 + :比如: 'A'+'B' = 'AB' |
取余数: mod(9,4) = 1 | 取余数: 9%4 = 1 |
1、对于字符型数据,空串''等同于NULL; 2、默认null排在非null之后,可通过nulls first或者nulls last来指定 | 1、对于字符型数据,空串'' 不同于null; 2、null排在非null之前 |
oracle 10不支持,oracle11支持,语法: select ... from (select ... from 源表) as 别名1 pivot ( 聚合函数(需要做聚合运算的列名) for 透视列名 in ('值1' as 列名1,'值2' as 列名2,...) ) AS 结果别名2 | select ... from (select ... from 源表) as 别名1 pivot ( 聚合函数(需要做聚合运算的列名) for 透视列名 in ([值1],[值2],...) ) AS 结果别名2 |
oracle 10不支持,oracle11支持,语法 select .. from table1 unpivot (列名 for 透视列 in ('值1','值2',..) ) as 别名2 | select .. from table1 unpivot (列名 for 透视列 in ([值1],[值2],..) ) as 别名2 |
Oracle与SQLServer的SQL语法差异
于 2024-04-24 08:48:00 首次发布