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

最新推荐文章于 2022-11-06 21:54:23 发布

wxz20032003

最新推荐文章于 2022-11-06 21:54:23 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： join date string function null c

本文链接：https://blog.youkuaiyun.com/wxz20032003/article/details/1336798

数据库专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种通过SQL查询来获取研究生导师年龄分布的方法。利用Oracle数据库函数进行数据处理，实现了从不同表中联接数据并计算导师年龄，进而按年龄段进行统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询目标：研究生指导教师(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