sql中根据查询出的数据新增行

[风一样的大叔]

数据库中原有数据查询后如下
ID rodno TestProject TestTime 1 2 3 4 5
1 A01P0123 Project1 2012-09-11 11:21:14 1 3 2 6 0.5
2 A01P0123 Project2 2012-09-11 11:21:14 2 1.2 1.5 6 3.2
3 A01P0123 Project3 2012-09-11 11:21:14 1.9 1.6 0.5 6.8 10.6
4 A01P0127 Project1 2012-09-11 11:21:14 6 5 2.2 1.2 2.5
5 A01P0127 Project2 2012-09-11 11:21:14 2 2 2.0 2.3 0.9
6 A01P0127 Project3 2012-09-11 11:21:14 2 2 2.0 2.3 0.9

我现在想依据TestProject中 Project1的值，将Project1后1到5个记录点的数据同时乘以2得到一个新行，TestProject命名为 Project4


如下：
ID rodno TestProject TestTime 1 2 3 4 5
1 A01P0123 Project1 2012-09-11 11:21:14 1 3 2 6 0.5
2 A01P0123 Project2 2012-09-11 11:21:14 2 1.2 1.5 6 3.2
3 A01P0123 Project3 2012-09-11 11:21:14 1.9 1.6 0.5 6.8 10.6
4 A01P0123 Project4 2012-09-11 11:21:14 2 6 4 12 1
5 A01P0127 Project1 2012-09-11 11:21:14 6 5 2.2 1.2 2.5
6 A01P0127 Project2 2012-09-11 11:21:14 2 2 2.0 2.3 0.9
7 A01P0127 Project3 2012-09-11 11:21:14 2 2 2.0 2.3 0.9
8 A01P0127 Project4 2012-09-11 11:21:14 12 10 4.4 2.4 5

[發糞塗牆]

按照你的数据不是你说的那样,你只是想把proaject1的数据乘以二再插入而已,如果是这样,很简单:

--先取消自增长
 SELECT * INTO #t FROM testproject
 TRUNCATE TABLE testproject
 
 INSERT INTO testproject(rodno,testproject ,testtime,[1],[2],[3],[4],[5])
 SELECT rodno,testproject ,testtime,rodno,testproject ,testtime,[1],[2],[3],[4],[5]
 FROM #t
 WHERE testproject<>'Project1'
 UNION ALL
 SELECT rodno,testproject ,testtime,[1]*2,[2]*2,[3]*2,[4]*2,[5]*2
 FROM #t
 WHERE testproject='Project1'
 
 
 --然后
 SELECT * FROM TestProject
 ORDER BY rodno ,TestProject

[DBA_磊仔]

--用公用表达式,以下是伪代码
;with CET1 AS(
ID rodno TestProject TestTime 1 2 3 4 5
1 A01P0123 Project1 2012-09-11 11:21:14 1 3 2 6 0.5 
2 A01P0123 Project2 2012-09-11 11:21:14 2 1.2 1.5 6 3.2 
3 A01P0123 Project3 2012-09-11 11:21:14 1.9 1.6 0.5 6.8 10.6 
4 A01P0127 Project1 2012-09-11 11:21:14 6 5 2.2 1.2 2.5 
5 A01P0127 Project2 2012-09-11 11:21:14 2 2 2.0 2.3 0.9 
6 A01P0127 Project3 2012-09-11 11:21:14 2 2 2.0 2.3 0.9 
),CET2 AS
(select 2*  from CET1 WHERE id = 5)
select * from CET1
UNION ALL
SELECT * FROM cet2