oracle sql 查询序号,Oracle SQL：经典查询练手第五篇

最新推荐文章于 2025-04-29 13:29:22 发布

庄泽曦

最新推荐文章于 2025-04-29 13:29:22 发布

阅读量278

点赞数

文章标签： oracle sql 查询序号

本文通过具体实例展示了如何使用SQL查询来解决实际业务问题，包括找出同一部门中入职晚但薪资更高的员工、筛选不在特定管理者所在部门工作的员工以及定位特定部门内的不同职位等。

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

/*-------6、列出在同一部门共事，入职日期晚但工资高于其他同事的员工：

名字、工资、入职日期(关联子查询)。---------*/

SQL>SELECTFIRST_NAME ||' '|| LAST_NAMEAS姓名,

2 SALARYAS工资,HIRE_DATEAS入职日期

3FROMEMPLOYEES EMP1

4WHEREEXISTS (

5SELECT1FROMEMPLOYEES EMP2

6WHEREEMP2.DEPARTMENT_ID = EMP1.DEPARTMENT_ID

7ANDEMP1.HIRE_DATE > EMP2.HIRE_DATE

8ANDEMP1.SALARY > EMP2.SALARY

9 );

姓名工资入职日期

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

Nancy Greenberg 12000.00 1994-8-17

Jose Manuel Urman 7800.00 1998-3-7

Shelli Baida 2900.00 1997-12-24

Adam Fripp 8200.00 1997-4-10

Matthew Weiss 8000.00 1996-7-18

Jennifer Dilly 3600.00 1997-8-13

Julia Dellinger 3400.00 1998-6-24

Laura Bissot 3300.00 1997-8-20

Kevin Mourgos 5800.00 1999-11-16

Shanta Vollman 6500.00 1997-10-10

Vance Jones 2800.00 1999-3-17

Anthony Cabrio 3000.00 1999-2-7

Girard Geoni 2800.00 2000-2-3

DouglasGrant2600.00 2000-1-13

Donald OConnell 2600.00 1999-6-21

Randall Perkins 2500.00 1999-12-19

Martha Sullivan 2500.00 1999-6-21

Kevin Feeney 3000.00 1998-5-23

Alana Walsh 3100.00 1998-4-24

Samuel McCain 3200.00 1998-7-1

Timothy Gates 2900.00 1998-7-11

Jean Fleaur 3100.00 1998-2-23

Winston Taylor 3200.00 1998-1-24

Michael Rogers 2900.00 1998-8-26

Britney Everett 3900.00 1997-3-3

Kelly Chung 3800.00 1997-6-14

Alexis Bull 4100.00 1997-2-20

Randall Matos 2600.00 1998-3-15

John Seo 2700.00 1998-2-12

Stephen Stiles 3200.00 1997-10-26

Mozhe Atkinson 2800.00 1997-10-30

Irene Mikkilineni 2700.00 1998-9-28

Julia Nayer 3200.00 1997-7-16

Hazel Philtanker 2200.00 2000-2-6

Ki Gee 2400.00 1999-12-12

Steven Markle 2200.00 2000-3-8

Sarah Bell 4000.00 1996-2-4

Nandita Sarchand 4200.00 1996-1-27

Lisa Ozer 11500.00 1997-3-11

Clara Vishney 10500.00 1997-11-11

Eleni Zlotkey 10500.00 2000-1-29

Gerald Cambrault 11000.00 1999-10-15

Alberto Errazuriz 12000.00 1997-3-10

Tayler Fox 9600.00 1998-1-24

Harrison Bloom 10000.00 1998-3-23

Danielle Greene 9500.00 1999-3-19

Charles Johnson 7211.00 2000-1-4

Mattea Marvins 7200.00 2000-1-24

Ellen Abel 11000.00 1996-5-11

Karen Partners 13500.00 1997-1-5

John Russell 14000.00 1996-10-1

Peter Tucker 10000.00 1997-1-30

David Bernstein 9500.00 1997-3-24

Jonathon Taylor 8600.00 1998-3-24

Alyssa Hutton 8800.00 1997-3-19

Peter Hall 9000.00 1997-8-20

Jack Livingston 8000.00 1998-4-23

Christopher Olsen 8000.00 1998-3-30

Elizabeth Bates 7300.00 1999-3-24

William Smith 7400.00 1999-2-23

Nanette Cambrault 7500.00 1998-12-9

61rowsselected

/*-------7、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)

不在同一个部门(非关联子查询)。---------*/

SQL>SELECTFIRST_NAME ||' '|| LAST_NAME

2FROMEMPLOYEES

3WHEREDEPARTMENT_ID <>

4 (SELECTDEPARTMENT_IDFROMEMPLOYEES

5WHEREFIRST_NAME ='Den'

6ANDLAST_NAME ='Raphaely'

7 );

FIRST_NAME||''||LAST_NAME

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

Steven King

Neena Kochhar

Lex De Haan

Alexander Hunold

Bruce Ernst

David Austin

Valli Pataballa

--等等

--或者

SQL>SELECTFIRST_NAME ||' '|| LAST_NAME

2FROMEMPLOYEES

3WHEREDEPARTMENT_IDNOTIN

4 (SELECTDEPARTMENT_IDFROMEMPLOYEES

5WHEREFIRST_NAME ='Den'

6ANDLAST_NAME ='Raphaely'

7 );

/*-------8、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)

不在同一个部门(关联子查询)。---------*/

SQL>SELECTFIRST_NAME ||' '|| LAST_NAME

2FROMEMPLOYEES EMP1

3WHERENOTEXISTS (

4SELECT1FROMEMPLOYEES EMP2

5WHEREEMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID

6ANDEMP2.FIRST_NAME ='Den'

7ANDEMP2.LAST_NAME ='Raphaely');

FIRST_NAME||''||LAST_NAME

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

KimberelyGrant

Lex De Haan

Neena Kochhar

Steven King

Pat Fay

Michael Hartstein

Diana Lorentz

Valli Pataballa

--等等

/*-------9、Finance部门有哪些职位(非关联子查询)。---------*/

SQL>SELECTDISTINCTJOB_IDFROMEMPLOYEES

2WHEREDEPARTMENT_ID = (

3SELECTDEPARTMENT_IDFROMDEPARTMENTS

4WHEREDEPARTMENT_NAME ='Finance');

JOB_ID

----------

FI_ACCOUNT

FI_MGR

--或者

SQL>SELECTDISTINCTJOB_IDFROMEMPLOYEES

2WHEREDEPARTMENT_IDIN(

3SELECTDEPARTMENT_IDFROMDEPARTMENTS

4WHEREDEPARTMENT_NAME ='Finance');

JOB_ID

----------

FI_ACCOUNT

FI_MGR

/*-------10、Finance部门有哪些职位(关联子查询)。---------*/

SQL>SELECTDISTINCTJOB_IDFROMEMPLOYEES

2WHEREEXISTS(

3SELECT1FROMDEPARTMENTS

4WHEREEMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

5ANDDEPARTMENTS.DEPARTMENT_NAME ='Finance');

JOB_ID

----------

FI_ACCOUNT

FI_MGR

oracle sql 查询 序号,Oracle SQL：经典查询练手第五篇

oracle sql 查询序号,Oracle SQL：经典查询练手第五篇