tb1
id name startamount endamount date
1 a 10 20 2007-09-09
2 b 11 25 2007-09-09
3 c 15 65 2007-09-09
-------------
n * 21 44 2007-09-09
1 a 22 20 2007-09-10
2 b 55 66 2007-09-10
3 c 89 77 2007-09-10
------------
n * 34 30 2007-09-10
要求让9月9号的endamount赋给9月10号的startamount,9月10号的startamount不要了,怎么做?先谢谢哈!
id name startamount endamount date
1 a 10 20 2007-09-09
2 b 11 25 2007-09-09
3 c 15 65 2007-09-09
-------------
n * 21 44 2007-09-09
1 a 22 20 2007-09-10
2 b 55 66 2007-09-10
3 c 89 77 2007-09-10
------------
n * 34 30 2007-09-10
要求让9月9号的endamount赋给9月10号的startamount,9月10号的startamount不要了,怎么做?先谢谢哈!
from tb1 as tb2
试试
update tb2 set tb2.startamount=(select endamount from tb1 where tb1.id= tb2.id and tb1.date='2007-09-09')
from tb1 as tb2
where date='2007-09-10'
SET startmount=(SELECT endamount FROM tb1 WHERE NAME=TB1.NAME AND date='2007-09-09')
WHERE date='2007-09-10'
INSERT TB1
SELECT 1 ,'a', 10 , 20 ,'2007-09-09'
UNION SELECT 2 ,'b', 11 , 25 ,'2007-09-09'
UNION SELECT 3 ,'c', 15 , 65 ,'2007-09-09'
UNION SELECT 1 ,'a', 22 , 20 ,'2007-09-10'
UNION SELECT 2 ,'b', 55 , 66 ,'2007-09-10'
UNION SELECT 3 ,'c', 89 , 77 ,'2007-09-10'UPDATE A
SET A.startamount=B.endamount
FROM TB1 A INNER JOIN (SELECT * FROM TB1 WHERE [date]='2007-09-09') B ON A.ID=B.ID
WHERE A.[date]='2007-09-10'SELECT * FROM TB1 ORDER BY DATE,IDDROP TABLE TB1/* 结果id name startamount endamount date
----------- ---------- ----------- ----------- -----------------------
1 a 10 20 2007-09-09 00:00:00.000
2 b 11 25 2007-09-09 00:00:00.000
3 c 15 65 2007-09-09 00:00:00.000
1 a 20 20 2007-09-10 00:00:00.000
2 b 25 66 2007-09-10 00:00:00.000
3 c 65 77 2007-09-10 00:00:00.000(6 row(s) affected)
*/
insert into tb1 select '1','a',10,20,'2007-09-09'
union all select '2','b',11,25,'2007-09-09'
union all select '3','c',15,65,'2007-09-09'
union all select 'n','*',21,44,'2007-09-09'
union all select '1','a',22,20,' 2007-09-10'
union all select '2','b',55,66,' 2007-09-10'
union all select '3','c',89,77,' 2007-09-10'
union all select 'n','*',34,30,' 2007-09-10'
update tb1 set startamount=(select endamount from tb1 where date='2007-09-09' and tb1.id=tb2.id) from tb1 as tb2 where date='2007-09-10'