oracle查询第四,Oracle SQL：经典查询练手第四篇(2)

知乎老王

于 2021-04-15 00:34:00 发布

阅读量190

点赞数

文章标签： oracle查询第四

本文展示了多个SQL查询示例，用于统计各部门的平均工资、最高工资、最低工资、人数，以及工资超过5000的员工数量。还详细分析了具有相同工资的员工分布，并列举了部门中工资超过1000且员工数超过2人的部门及其所在城市。

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

/*--------1、各个部门平均、最大、最小工资、人数，按照部门号升序排列。---------*/

SQL>SELECTDEPARTMENT_IDAS部门号,AVG(SALARY)AS平均工资

2 ,MAX(SALARY)AS最高工资,MIN(SALARY)AS最低工资

3 ,COUNT(*)AS人数

4FROMEMPLOYEES

5GROUPBYDEPARTMENT_ID

6ORDERBYDEPARTMENT_IDASC;

部门号平均工资最高工资最低工资人数

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

10 4400 4400 4400 1

20 9500 13000 6000 2

30 4150 11000 2500 6

40 6500 6500 6500 1

50 3475.55555 8200 2100 45

60 5760 9000 4200 5

70 10000 10000 10000 1

80 8973.85294 14000 6100 34

90 21333.3333 24000 20000 3

100 8600 12000 6900 6

110 10150 12000 8300 2

7000 7000 7000 1

12rowsselected

/*--------2、各个部门中工资大于5000的员工人数。---------*/

SQL>SELECTDEPARTMENT_ID,COUNT(*)FROMEMPLOYEES

2WHERESALARY > 5000

3GROUPBYDEPARTMENT_ID;

DEPARTMENT_IDCOUNT(*)

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

20 2

30 1

40 1

50 5

60 2

70 1

80 34

90 3

100 6

110 2

11rowsselected

/*--------3、各个部门平均工资和人数，按照部门名字升序排列。---------*/

SQL>SELECTDPTNAME,AVG(SALARY),COUNT(*)FROM

2 (SELECT

3 (SELECTDEPT.DEPARTMENT_NAMEFROMDEPARTMENTS DEPT

4WHEREDEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,

5 EMP.SALARY

6FROMEMPLOYEES EMP)

7GROUPBYDPTNAME

8ORDERBYDPTNAME;

DPTNAMEAVG(SALARY)COUNT(*)

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

Accounting 10150 2

Administration 4400 1

Executive 21333.33333 3

Finance 8600 6

Human Resources 6500 1

IT 5760 5

Marketing 9500 2

PublicRelations 10000 1

Purchasing 4150 6

Sales 8973.852941 34

Shipping 3475.555555 45

7000 1

12rowsselected

--或者--

SQL>SELECTDEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)

2FROMEMPLOYEES EMP,DEPARTMENTS DEPT

3WHEREEMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID

4GROUPBYDEPT.DEPARTMENT_NAME

5ORDERBYDEPT.DEPARTMENT_NAME;

DEPARTMENT_NAMEAVG(EMP.SALARY)COUNT(*)

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

Accounting 10150 2

Administration 4400 1

Executive 21333.333333333 3

Finance 8600 6

Human Resources 6500 1

IT 5760 5

Marketing 9500 2

PublicRelations 10000 1

Purchasing 4150 6

Sales 8973.8529411764 34

Shipping 3475.5555555555 45

11rowsselected

--可以看到，这种方式，对于部门号为空的没有统计出来

/*--------4、列出每个部门中有同样工资的员工的统计信息，

列出他们的部门号，工资，人数。---------*/

SQL>SELECTEMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT

2FROMEMPLOYEES EMP1,EMPLOYEES EMP2

3WHEREEMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_IDAND

4 EMP1.SALARY = EMP2.SALARY

5ANDEMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID

6GROUPBYEMP1.DEPARTMENT_ID,EMP1.SALARY;

DEPARTMENT_ID SALARY CNT

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

50 2200.00 2

50 2400.00 2

50 2500.00 20

50 2600.00 6

50 2700.00 2

50 2800.00 6

50 2900.00 2

50 3000.00 2

50 3100.00 6

50 3200.00 12

50 3300.00 2

50 3600.00 2

60 4800.00 2

80 7000.00 2

80 7500.00 2

80 8000.00 6

80 9000.00 2

80 9500.00 6

80 10000.00 6

80 10500.00 2

80 11000.00 2

90 20000.00 2

22rowsselected

/*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门，

显示部门名字、地区名称。---------*/

SQL>SELECTD.DEPARTMENT_NAME,L.CITY,COUNT(*)

2FROMEMPLOYEES E,DEPARTMENTS D,LOCATIONS L

3WHEREE.DEPARTMENT_ID = D.DEPARTMENT_IDAND

4 D.LOCATION_ID = L.LOCATION_IDAND

5 E.SALARY > 1000

6GROUPBYD.DEPARTMENT_NAME,L.CITY

7HAVINGCOUNT(*) > 2;

DEPARTMENT_NAME CITYCOUNT(*)

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

IT Southlake 5

Sales Oxford 34

Finance Seattle 6

Shipping South San Francisco 45

Executive Seattle 3

Purchasing Seattle 6

6rowsselected