根据与作业SQL相同的名称筛选数据

Question

大家好，我目前正在处理一个问题，我想看看是否有办法做到这一点

一个工作人员想要看到一个报告，该报告显示了她所从事的所有工作的小时数，加上所有其他工作人员也从事这些工作的小时数。如果我的数据是这样的

Client-----     Job------   Staff------     Hours    
A-----------    1 --------  Caty----------  15    
A-----------    1 --------  John----------  10    
A-----------    1 --------  Greg----------  6     
B-----------    2 --------  Caty----------  8    
B-----------    2 --------  Ralph-------    10    
B-----------    2 --------  Derek-------    12     
C-----------    1 --------  Steve -------   9      
C-----------    1 --------  Bill----------  20     
C-----------    1 --------  Mike--------    18     

对于凯蒂，报告应该给我这个:

Client-----     Job------   Staff------     Hours    
A-----------    1 --------  Caty----------  15    
A-----------    1 --------  John----------  10    
A-----------    1 --------  Greg----------  6     
B-----------    2 --------  Caty----------  8    
B-----------    2 --------  Ralph-------    10    
B-----------    2 --------  Derek-------    12

我不能放入WHERE子句"WHERE Staff= ' Caty '"，因为这会过滤掉John, Greg, Ralph和Derek，只留下Caty的小时数。

select clientcode, clientname, job_name, staffcode, sum(wiphours) as Hours
from tbltranwip w
inner join tblengagement e on e.ContIndex=w.ContIndex
inner join tblJob_Header h on h.Job_Idx=w.ServPeriod
inner join tblstaff s on s.StaffIndex=w.StaffIndex
where wipdate between 'jul 1 2015' and 'jul 31 2015' and TransTypeIndex=1 
and w.ContIndex<900000 and staffcode = 'Caty'
group by clientcode, clientname, job_name, staffcode

代码只显示了我的名字

Answer 1

假设您的表或视图是这样的:

CREATE TABLE job(
    Client char(1) NOT NULL,
    Job int NOT NULL,
    Staff varchar(20) NOT NULL,
    Hours int NOT NULL
);
insert into job values
('A',1, 'Caty', 15),
('A',1, 'John', 10),
('A',1, 'Greg', 6),
('B',2, 'Caty', 8),
('B',2, 'Ralph', 10),
('B',2, 'Derek', 12),
('C',1, 'Steve', 9),
('C',1, 'Bill', 20),
('C',1, 'Mike', 18);

您可以编写这样的查询来检索所需的信息:

select client, job, staff, sum(hours) as totalhours
from job x
where exists (
    select 1 
    from job 
    where staff = 'Caty'
    and client = x.client
    and job = x.job)
group by client, job, staff

结果:

client job         staff                totalhours
------ ----------- -------------------- -----------
A      1           Caty                 15
A      1           Greg                 6
A      1           John                 10
B      2           Caty                 8
B      2           Derek                12
B      2           Ralph                10

SQLFiddle示例:http://sqlfiddle.com/#!6/88075/1

可能尝试这个来解决你的问题

with data as (
    select clientcode, clientname, job_name, staffcode, sum(wiphours) as Hours
    from tbltranwip w
    inner join tblengagement e on e.ContIndex=w.ContIndex
    inner join tblJob_Header h on h.Job_Idx=w.ServPeriod
    inner join tblstaff s on s.StaffIndex=w.StaffIndex
    where wipdate between 'jul 1 2015' and 'jul 31 2015' and TransTypeIndex=1 
    and w.ContIndex<900000
    group by clientcode, clientname, job_name, staffcode
)
select clientcode, job_name, staffcode, sum(wiphours) as Hours
from data x
where exists (
    select 1 
    from data
    where staffcode = 'Caty'
    and clientcode= x.clientcode
    and job_name= x.job_name)
group by clientcode, job_name, staffcode

Answer 2

您可以在子查询中选择您所参与的作业，您可以加入子查询以获取结果

Select sum(hours) from timetable t
inner join (select client, job from timetable where staff = 'caty') as sub 
on t.client = sub.client and t.job = sub.job

这应该可以让你开始

Answer 3

要获得与Caty从事相同工作的人员，您将需要使用in谓词和子查询;就像

where ...
and job in (select job from appropriate_table where Staff='Caty')

我不确定你的表是如何关联的，所以你会想根据需要调整代码，但它应该给你一个想法。

Answer 4

在where子句中试试:staffcode = 'Caty'或staffcode in(选择与Caty相同组的员工)