多表查询,join,内联接,substr,字符串连接,to_char,Sysdate,to_date,嵌套查询

查询目标：研究生指导教师(B表)类型名称DSLBMC、年龄段NLD(C表中出生日期CSRQ格式1960-05-18)
-------------------------------------------------------------------------------------------------------

表：

hl_usr_zxbz.T_ZXBZ_DSLB                 导师类别(类型名称,类型码)              a
hl_usr_gxsj.T_JZG_YJSDSXX       研究生导师信息(类型码, 职工号)          b
hl_usr_GXSJ.T_JZG_JBXX             教职工基本信息(职工号,出生日期)       c

---------------------------------------------------------------------------------------------------
表关系：

a                                                       b                                                      c

类型名称:DSLBMC           

类型码:DSLBDM  -----------> 类型码:DSLBDM 

                                                职工号：ZGH        ----------->     职工号：ZGH

                                                                                                     出生日期:CSRQ

---------------------------------------------------------------------------------------------------

常用语法：

'%'||?||'%'：字符串连接

to_char(csrq,'yyyy')：转化日期格式 年、年月yyyy-mm、年月日 yyyy-mm-dd、小时 'hh'、24小时制 'hh24'

Sysdate：当前日期

上个月：Select Sysdate - Interval '1' Month From Dual  或 add_months(Sysdate,-1)to_date(?,'yyyy-mm-dd')
substr(jszw,-1,1)
substr((to_date(substr(?,1,10),'yyyy-mm-dd')-cssj)/365,0,2) as age

In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.
The syntax for the substr function is:
substr( string, start_position, [ length ] )
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts backwards.
If length is a negative number, then substr will return a NULL value.

For example:

substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', 1, 4) would return 'Tech'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'
substr('TechOnTheNet', -8, 2) would return 'On'

-----------------------------------------------------------------------------------------------------

先查年龄：                            

Select a.dslbmc,to_char(sysdate,'yyyy')-to_char(c.csrq,'yyyy') As age

From hl_usr_zxbz.T_ZXBZ_DSLB a

Join hl_usr_gxsj.T_JZG_YJSDSXX b On a.dslbdm=b.dslbdm

Join hl_usr_GXSJ.T_JZG_JBXX c On b.zgh=c.zgh

               

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

再查年龄段：nld

Select dslbmc,substr(age,1,1)||'0'||'－'||to_char(to_number(substr(age,1,1))+1)||'0' As nld,Count(*) As Count,'0' As flag
From (
Select a.dslbmc,to_char(sysdate,'yyyy')-to_char(c.csrq,'yyyy') As age
From hl_usr_zxbz.T_ZXBZ_DSLB a
Join hl_usr_gxsj.T_JZG_YJSDSXX b On a.dslbdm=b.dslbdm
Join hl_usr_GXSJ.T_JZG_JBXX c On b.zgh=c.zgh
)
Where age Between 20 And 80
Group By dslbmc,substr(age,1,1)

————————————————————————————————

结果：

          DSLBMC            NLD             COUNT      FLAG

1         学术(论文)       20－30              1                0
2         应用(授课)       20－30              1                0
3         应用(授课)       20－30              1                0

作者：汪湘洲

06.10.16