CREATE TABLE taskproc_week
(
Id int, --普通ID
PId int, --步骤ID
week int, --周
planNum int, --数量
projId int, --项目ID
taskId int --作业ID
)
---测试数据
INSERT INTO taskproc_week select 1,101,1,0,500,20
INSERT INTO taskproc_week select 2,102,1,0,500,20
INSERT INTO taskproc_week select 3,103,1,0,500,30
INSERT INTO taskproc_week select 4,104,1,0,500,30
INSERT INTO taskproc_week select 5,101,2,0,500,20
INSERT INTO taskproc_week select 6,106,2,0,600,40
-- SELECT * FROM taskproc_week
CREATE TABLE taskproc
(
pid int, --步骤ID
projId int, --项目ID
taskId int, --作业ID
cs int, --参数
sl int, --数量
pname nvarchar(50) --步骤名
)
---测试数据
INSERT INTO taskproc select 101,500,20,80,10,'电工'
INSERT INTO taskproc select 102,500,20,50,20,'管工'
INSERT INTO taskproc select 103,500,30,50,50,'管工'
INSERT INTO taskproc select 104,500,30,20,20,'保温工'
INSERT INTO taskproc select 106,600,40,30,60,'电焊工'
--SELECT * FROM taskproc
--问题：
--条件: 项目ID=500 , 周=1 ( planNum =cs*sl)
---想得到如何下数据 (并更新到 taskproc_week中)
--1 101 1 800 500 20
--2 102 1 100 500 20
--3 103 1 250 500 30
--4 104 1 40 500 30
-- 谢谢 大家
------解决方案--------------------
就是用这个进行更新：
--条件: 项目ID=500 , 周=1 ( planNum =cs*sl)
--更新
update taskproc_week
set planNum = cs*sl
from taskproc_week tw
inner join taskproc tp
on tw.projId = tp.projId and
tw.taskId = tp.taskId and
tw.PId = tp.pid
where tw.projId = 500 and tw.week = 1
select *
from taskproc_week
/*
Id PId week planNum projId taskId
1 101 1 800 500 20
2 102 1 1000 500 20
3 103 1 2500 500 30
4 104 1 400 500 30
5 101 2 0 500 20
6 106 2 0 600 40
*/