字符连接:
concat()
字符连接,并实现行列转换[在plsql中]:
wmsys.wm_concat() :
e.g:
字符截取
Substr() [SQLServer/plsql]:
e.g:
e.g:
e.g
e.g:
e.g:
e.g:
e.g:
字符连接,并实现行列转换[在plsql中]:
wmsys.wm_concat() :
e.g:
select time,name,to_char(wmsys.wm_concat(itemname)) AS item
from dba_users
group by time,name --用to_char可避免出现<clob>
??“wmsys.wm_concat对象实现行列转换的方法,这种方法不被Oracle所推荐,因为WMSYS用户用于Workspace Manager,其函数对象可能因版本而不同,这种变化在11.2.0.3及10.2.0.5中体现出来 ”
字符截取
Substr() [SQLServer/plsql]:
e.g:
select a.time,a.name,substr(a.detail,0,25)||'...' as detail
from dba_users
字符填充,并字符截取:
Lpad() / Rpad():e.g:
select name,age,lpad(seq,10,'aaa')
from dba_users --10为填充后返回的字符串长度
e.g:
select name,age
from dba_users
where lpad(age,2)<=18 --字符截取两个字符,中文占两个字符
对给定的链接服务器执行指定的传递查询:
openquery():e.g
SELECT a.action,t1.*
FROM OPENQUERY([dba],'SELECT seq,age,name,substr(a.detail,0,25)||''...'')FROM dba_users') AS t1 --''中的要用''的数据要用两对''
left join a on a.num = t1.seq
返回两个日期之间的时间
datediff(datepart,startdate,enddate):e.g:
SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate
--datepart可以是年月日季度等
数据类型的转换(sql server):
cast():e.g:
SELECT CAST(SUM(day,ksrq,jsrq)*1.0 / COUNT(seq) AS DECIMAL(12,2)) AS average_days
FROM dba_users
convert():
e.g:
CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(24),GETDATE(),113)
CONVERT(VARCHAR(10),ksrq,110)
数据类型的转换(Oracle):
to_char():
e.g: CAST(to_char(getdate(),'yyyymmdd')/100 AS int) AS 月份
字符替换或表转置:
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else ):e.g:
SELECT to_char(ksrq,'yyyy.mm')AS ksrq,
SUM(DECODE(dg_code,is not null,1))组内,
COUNT(DECODE(dg_code,null,0)) 非组内
FROM dba_users
GROUP BY to_char(tran_date,'yyyy.mm');