sql中根据查询出的数据新增行
数据库中原有数据查询后如下
ID rodno TestProject TestTime 1 2 3 4 5
1A01P0123Project12012-09-11 11:21:1413260.5
2A01P0123Project22012-09-11 11:21:1421.21.563.2
3A01P0123Project32012-09-11 11:21:141.91.60.56.810.6
4A01P0127Project12012-09-11 11:21:14652.21.22.5
5A01P0127Project22012-09-11 11:21:14222.02.30.9
6A01P0127Project32012-09-11 11:21:14222.02.30.9

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


如下：
ID rodno TestProject TestTime 1 2 3 4 5
1A01P0123Project12012-09-11 11:21:1413260.5
2A01P0123Project22012-09-11 11:21:1421.21.563.2
3A01P0123Project32012-09-11 11:21:141.91.60.56.810.6
4A01P0123Project42012-09-11 11:21:14264121
5A01P0127Project12012-09-11 11:21:14652.21.22.5
6A01P0127Project22012-09-11 11:21:14222.02.30.9
7A01P0127Project32012-09-11 11:21:14222.02.30.9
8A01P0127Project42012-09-11 11:21:1412104.42.45
[最优解释]
按照你的数据不是你说的那样,你只是想把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

--用公用表达式,以下是伪代码
;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