sql - 如何为以下问题做出选择语句？

Question

很抱歉问了一个简单的问题，但是您如何创建此 Select 语句以及您的思考过程是什么？我列出了我认为此 select 语句需要的下表。

问题：创建一个 SELECT 语句以显示每个员工请求服务的次数列表。显示员工 id、员工姓名、service_no、服务描述和请求的总次数。

Table: SERVICES
Column Name || Constraints || Default Value || Data Type || Length
SERVICE_NO  || Primary Key ||               || VarChar2  || 10
DESCRIPTION ||             ||               || VarChar2  || 50
CONTRACTOR  ||             ||               || VarChar2  || 20
CCONTACT_NO ||             ||               || VarChar2  || 10

Table: SERVICE_REQUEST
Column Name  || Constraints                          || Default Value || Data Type || Length
SR_ID        || Primary Key                          ||               || Number    || 10
SERVICE_NO   || Foreign Key to the SERVICES table    ||               || VarChar2  || 10
STAFF_ID     || Foreign Key to the STAFF table       ||               || VarChar2  || 10
TEC_ID       || Foreign Key to the TECHNICIANS table ||               || VarChar2  || 10
REQUEST_DATE ||                                      ||               || Date      ||
REQUEST_TIME ||                                      ||               || VarChar2  || 10

Table: STAFF
Column Name || Constraints                       || Default Value || Data Type || Length
STAFF_ID    || Primary key                       ||               || VarChar2  || 10
SNAME       ||                                   ||               || VarChar2  || 30
SIC_NO      || Secondary key                     ||               || VarChar2  || 10
SADDRESS    ||                                   ||               || VarChar2  || 70
SPHONE      ||                                   ||               || VarChar2  || 8
POSITION    ||                                   ||               || VarChar2  || 30
HIRE_DATE   ||                                   ||               || Date      || 
SALARY      ||                                   ||               || Number    || 7,2
SCH_ID      || Foreign Key to the SCHOOL table   ||               || VarChar2  || 10

标签： sqloraclesubquery

Answer 1

使用查询。

select s.staff_id, 
s.sname,    
ser.service_no,
ser.description, 
(select count(service_no) from staff where service_no = ser )
 from service_request sr ,services ser
where  s.staff_id = sr.staff_id 
and ser.service_no = sr.service_no;