SQL 查询 - 复制同一表中的值 [英] SQL Query - Copy Values in Same Table
问题描述
所以我有一个看起来像这样的表:
<前>项目 ID 任务 ID 日期000 001 10/28/09000 002 12/1/09000 003 02/24/10001 001 04/14/10001 002 07/5/10001 003 02/24/11002 001 06/07/10002 002 07/7/10002 003 09/13/12假设有很多项目和很多任务，但每个项目都包含相同的任务.我想以下列方式复制日期:
一个项目 (000) 具有主日期.我想将每个任务的这些日期复制到其他几个项目(示例数据中的 001、002).
因此，如果项目 000 中的任务 001 的日期为 10/28/09，我希望其他项目中的任务 001(我知道他们的 ID)具有相同的日期.
如果项目 001 中的任务 002 的日期是 12/1/09，我希望任务 002 在其他每个项目中也有这个日期.
有没有一种查询可以做到这一点?我认为应该有，但我现在无法弄清楚.
顺便说一句，我使用的是 SqlServer 2005.
UPDATE dbo.ProjectsSET 日期 = (从 dbo.Projects p1 中选择日期哪里 p1.ProjectID = '000' AND p1.TaskID = dbo.Projects.TaskID)WHERE ProjectID IN ('P01', 'P02', 'P03', 'P04')AND TaskID IN ('001', '002', '003')这对你有用吗?
因此对于 TaskID = 001，此语句将
SELECT Date FROM dbo.Projects WHERE ProjectID = '000' AND TaskID = '001'然后将每个项目 (P01-P04) 和 TaskID=001 更新到此日期.这就是你要找的，不是吗?
您还可以使用 OUTPUT 子句向您证明正在更新的内容:
更新 dbo.ProjectsSET 日期 = (从 dbo.Projects p1 中选择日期哪里 p1.ProjectID = '000' AND p1.TaskID = dbo.Projects.TaskID)OUTPUT 插入的项目ID、插入的任务ID、插入的日期WHERE ProjectID IN ('P01', 'P02', 'P03', 'P04')AND TaskID IN ('001', '002', '003')这将导致每行被更新以输出新值(例如在更新之后)，因此您可以检查并查看更新的内容以及更新的值.
刚刚运行查询，这是我得到的结果:
ProjectID TaskID 日期0 1 2009-10-28 00:00:00.0000 2 2009-12-01 00:00:00.0000 3 2010-02-24 00:00:00.0001 1 2009-10-28 00:00:00.0001 2 2009-12-01 00:00:00.0001 3 2010-02-24 00:00:00.0002 1 2009-10-28 00:00:00.0002 2 2009-12-01 00:00:00.0002 3 2010-02-24 00:00:00.000项目 1 和 2 以及任务 1、2、3 的日期已设置为主"项目 0 的值.
So I have a table that looks like this:
ProjectID TaskID Date 000 001 10/28/09 000 002 12/1/09 000 003 02/24/10 001 001 04/14/10 001 002 07/5/10 001 003 02/24/11 002 001 06/07/10 002 002 07/7/10 002 003 09/13/12
Assume there are many projects and many tasks, but each project contains the same tasks. I want to copy dates in the following manner:
One project (000) has the master dates. I want to copy those dates for each task to several other projects (001, 002 in the example data).
So, if task 001 in project 000 has a date of 10/28/09, I want task 001 in other projects (I know their IDs) to have the same date.
If task 002 in project 001 has date 12/1/09, I want task 002 to have this date in each of the other projects as well.
Is there one query that can do this? I think there should be, but I can't figure it out right now.
BTW I am using SqlServer 2005.
UPDATE dbo.Projects
SET Date = (SELECT Date FROM dbo.Projects p1
WHERE p1.ProjectID = '000' AND p1.TaskID = dbo.Projects.TaskID)
WHERE ProjectID IN ('P01', 'P02', 'P03', 'P04')
AND TaskID IN ('001', '002', '003')
Would that do it for you?
So for TaskID = 001, this statement will
SELECT Date FROM dbo.Projects WHERE ProjectID = '000' AND TaskID = '001'
and then update each of the projects (P01-P04) and TaskID=001 to this date. That's what you're looking for, no??
You can also use an OUTPUT clause to PROVE to you what is being updated:
UPDATE dbo.Projects
SET Date = (SELECT Date FROM dbo.Projects p1
WHERE p1.ProjectID = '000' AND p1.TaskID = dbo.Projects.TaskID)
OUTPUT inserted.ProjectID, inserted.TaskID, inserted.Date
WHERE ProjectID IN ('P01', 'P02', 'P03', 'P04')
AND TaskID IN ('001', '002', '003')
This will cause each row being updated to output the new values (e.g. after the update) so you can check and see what gets updated, and to what values.
Just ran the query and this is the results I get:
ProjectID TaskID Date
0 1 2009-10-28 00:00:00.000
0 2 2009-12-01 00:00:00.000
0 3 2010-02-24 00:00:00.000
1 1 2009-10-28 00:00:00.000
1 2 2009-12-01 00:00:00.000
1 3 2010-02-24 00:00:00.000
2 1 2009-10-28 00:00:00.000
2 2 2009-12-01 00:00:00.000
2 3 2010-02-24 00:00:00.000
The dates for Projects 1 and 2 and Tasks 1,2,3 have been set to the values of the "master" project 0.
这篇关于SQL 查询 - 复制同一表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
