跨多个已连接表的SQL查询

I've got three tables: Resource, Timesheet, TimePeriod. A Resource has many Timesheets and a Timesheet has one TimePeriod. This used for us to keep track of employee timesheets. I'm trying to figure out how to find out the status of each person's timesheet for this week. The problem is, if a person hasn't filled out a timesheet, then there will not be an entry in the timesheet table. So their status should be NULL.

我有三个表:资源、时间表、时间段。资源有许多时间表，而时间表有一个时间段。这是用来跟踪员工工时表的。我想弄清楚这周每个人的时间表的状态。问题是，如果一个人没有填写时间表，那么时间表表中就不会有条目。所以它们的状态应该是零。

This is what the database looks like:

数据库是这样的:

Resource
id | Name 
---+------------
 1 | John Smith
 2 | Jason Bourne

Timesheet
id | Status    | Resource_Id |TimePeriod_Id
---+-----------+-------------------------------
 1 | Submitted | 1           | 1
 2 | Created   | 1           | 2
 3 | Submitted | 2           | 1


TimePeriod
id | Week 
---+----------------
 1 | 2013Week1
 2 | 2013Week2
 3 | 2013Week3

If the TimePeriod were stored in the same table then this wouldn't be a problem. But since its in a separate table I think there is a problem with the way I'm doing my joins. I can't figure out the query to make this work.

如果时间周期存储在同一个表中，那么这就不是问题。但是由于它是在一个单独的表中，我认为我的连接方式有问题。我想不出要做这项工作的疑问。

I have tried this:

我试过这个:

SELECT res.id, res.name, ts.status
FROM Resource res
LEFT JOIN Timesheet ts ON ts.resource_id = res.id
LEFT JOIN TimePeriod tp ON ts.timeperiod_id = tp.id 
WHERE tp.week = '2013Week2'

This obviously eliminates Jason Bourne from the results because he has no timesheet

这显然使杰森·伯恩从结果中消失，因为他没有时间表

I also tried this:

我也试过这样的:

SELECT res.id, res.name, ts.status
FROM Resource res
LEFT JOIN Timesheet ts ON ts.resource_id = res.id
LEFT JOIN TimePeriod tp ON ts.timeperiod_id = tp.id AND tp.week = '2013Week2'

Which returns extra rows and wrong data.

返回额外的行和错误的数据。

The desired result would be:

期望的结果是:

id  name           status    
1   John Smith     Created
2   Jason Bourne   NULL  

I believe I could stumble my way through this with UNION, but I feel like there should be a way to do this other than that. If anyone has any advice I would really appreciate it. Thanks.

我相信我可以和工会一起跌撞撞地走过这段路，但我觉得应该有别的办法。如果有人有什么建议，我将非常感激。谢谢。

7 个解决方案

#1

Another one:

另一个:

SELECT r.id, r.Name, s.Status
FROM Resource r
INNER JOIN TimePeriod p ON p.week = '2013week2'
LEFT JOIN Timesheet s ON s.Resource_id = r.id AND s.TimePeriod_id = p.id
;

Alternatively you could replace the INNER JOIN ... ON with a CROSS JOIN ... WHERE:

或者您可以替换内部连接……在交叉连接上……地点:

SELECT r.id, r.Name, s.Status
FROM Resource r
CROSS JOIN TimePeriod p
LEFT JOIN Timesheet s ON s.Resource_id = r.id AND s.TimePeriod_id = p.id
WHERE p.week = '2013week2'
;

Although it must be said that MySQL doesn't distinguish between CROSS JOIN and INNER JOIN, treating those as synonyms of each other. Anyway, the above queries are standard SQL and would work in any SQL product.

尽管MySQL不能区分交叉连接和内部连接，但可以将它们视为彼此的同义词。无论如何，以上查询都是标准SQL，在任何SQL产品中都可以使用。

A SQL Server demo at SQL Fiddle: http://sqlfiddle.com/#!3/6a0a1/2

SQL小提琴的SQL Server演示:http://sqlfiddle.com/#!3/6a0a1/2

#2

You need to a LEFT JOIN the combination of Timesheet and the inner join to TimePeriod

您需要向左连接时间表和内部连接到TimePeriod的组合。

The syntax for that is

它的语法是

SELECT res.id, res.name, ts.status
FROM Resource res
LEFT JOIN (Timesheet ts 
             INNER JOIN TimePeriod tp 
            ON ts.timeperiod_id = tp.id AND tp.week = '2013Week2')
ON ts.resource_id = res.id

You might also want to do COALESCE(ts.status, 'unsubmitted') status to convert the nulls

您可能还想合并(ts)。状态，'未提交')状态转换为空。

SQL Fiddle

SQL小提琴

#3

Try this.

试试这个。

SELECT res.id, res.name, ts.status
FROM [Resource] res
INNER JOIN Timesheet ts ON ts.resource_id = res.id
LEFT JOIN TimePeriod tp ON ts.timeperiod_id = tp.id 
WHERE tp.week = '2013Week2'

This is of course without creating the data locally. If this doesn't work let me know and I'll give it a second try and recreate your structure locally.

这当然不需要在本地创建数据。如果这不起作用，请告诉我，我再试一次，在本地重新创建您的结构。

#4

Try joining from your resource table instead

尝试从资源表中加入

SELECT res.id, res.name, ts.status
FROM Resource res
RIGHT JOIN Timesheet ts ON ts.resource_id = res.ID
LEFT JOIN TimePeriod tp on ts.timeperiod_id = tp.id AND tp.week - '2013Week2'

That way you should get existant and non existant timesheets for all resources that exist, and only timesheets for your given time period.

通过这种方式，您应该获得存在的所有资源的存在和不存在的时间表，并且在给定的时间段内只获取时间表。

#5

Looks like this works. Reference the SQLfiddle posted below.

像这样的作品。引用下面发布的SQLfiddle。

select r1.id, r1.name, ts.status
from resource r1, resource r2, timesheet ts
where r1.id = r2.id (+) 
and r2.id = ts.resource_id
and ts.timeperiod_id = (select max(id) from timeperiod)

EDIT: using a more explicit join syntax:

编辑:使用更明确的连接语法:

SELECT r1.id, r1.name, ts.status
FROM resource r1
LEFT OUTER JOIN resource r2 ON r1.id = r2.id
JOIN timesheet ts ON r2.id = ts.resource_id
WHERE ts.timeperiod_id = :timeperiod   -- you can use whatever timeperiod you want, doesn't have to be the max(id)

Sqlfiddle: http://www.sqlfiddle.com/#!4/9dd71/3

Sqlfiddle:http://www.sqlfiddle.com/ ! 4/9dd71/3

#6

The code below create three local temp tables with the data your provided above.

下面的代码使用上面提供的数据创建三个本地临时表。

Many people do not know that cross apply can be used like a full outer join.

许多人不知道交叉应用可以作为一个完整的外部连接使用。

Thus, we want the cross product of the resource entries and the time period entries for this week. Regardless if someone submitted a time sheet.

因此，我们需要本周的资源项和时间段项的叉积。不管是否有人提交了时间表。

The next step is to left join onto the result on both resource id and time period from the time sheet. This will show both created and submitted entries.

下一步是将join放在时间表中资源id和时间段的结果上。这将显示已创建和已提交的条目。

That is assuming an update does not move a created to the submitted column. Adding another table with 1 = created and 2 = submitted and taking the max value can list the top one.

假设更新没有将创建的内容移动到提交的列中。添加另一个包含1 =创建和2 =提交的表，并使用max值可以列出上面的值。

The result is shown below.

结果如下所示。

John Miner, the craftydba, www.craftydba.com

craftydba网站www.craftydba.com

-- Resource table
create table #Resource
(res_id int, res_name varchar(128));

——资源表创建表#Resource (res_id int, res_name varchar(128));

-- Add data
insert into #Resource
values
(1 , 'John Smith'),
(2 , 'Jason Bourne');

——将数据插入#资源值(1，'John Smith')， (2， 'Jason Bourne');

-- Time period table
create table #TimePeriod
(tp_id int, tp_week int);

——工时期间表创建表#工时期间(tp_id int, tp_week int);

-- Add data
insert into #TimePeriod
values
(1 , 201301),
(2 , 201302),
(3 , 201303);

-添加数据插入#TimePeriod值(1,201301)，(2,201302)，(3,201303);

-- Time sheet table
create table #TimeSheet
(ts_id int, ts_status varchar(16), res_id int, tp_id int);

——工时表创建表#工时表(ts_id int, ts_status varchar(16)， res_id int, tp_id int);

-- Add data
insert into #TimeSheet
values
(1, 'Submitted', 1 , 1),
(2, 'Created', 1 , 2),
(3, 'Submitted', 2 , 1);

——将数据插入到#TimeSheet值中(1，' submit '， 1, 1)， (2， 'Created'， 1, 2)， (3， ' submit '， 2, 1);

-- Need both resource and period regardless of time period
select *
from #Resource r
cross apply
( select * from #TimePeriod p1 where p1.tp_week = 201302
) as ca_Period
left join #TimeSheet s on s.res_id = r.res_id and ca_Period.tp_id= s.tp_id

——需要资源和周期，而不考虑时间段，从# resource r cross apply中选择*(从#TimePeriod p1中选择*，其中p1。当ca_Period离开时，在s上加入#工时表s。res_id = r。res_id ca_Period。tp_id = s.tp_id

#7

Try this. It is tested with your data. But you won't get NULL Values for any resource.

试试这个。用你的数据进行测试。但是你不会得到任何资源的空值。

SELECT Resource.id,Resource.Name,TimeSheet.Status FROM Resource,TimeSheet,TimePeriod WHERE Resource.id = TimeSheet.id AND TimeSheet.TimePeriod_id = TimePeriod.id AND TimePeriod.Week="2013Week1"

选择Resource.id、Resource.Name时间表。来自资源、时间表、资源的时间段的状态。id =时间表。id和时间表。TimePeriod_id = TimePeriod。id和TimePeriod.Week = " 2013 week1”

Hope it helps.

希望它可以帮助。