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
