一个比较头疼的sql语句，在线请问

2012-03-18

一个比较头疼的sql语句，在线请教表1idfnumftest12aa213bb334cc表2NidFindexfdes1112kk2222mm3332ss把表2中

一个比较头疼的sql语句，在线请教
表1
id fnum ftest
1 2 aa
21 3 bb
33 4 cc

表2
Nid Findex fdes
11 12 kk
22 22 mm
33 32 ss

把表2中的Nid,fdes插入到表1中id，ftest
然后表1中的fnum要累加，fnum字段不是标识列
最终结果应该是
id fnum ftest
1 2 aa
21 3 bb
33 4 cc
11 5 kk
22 6 mm
33 7 ss

就写一个sql语句来实现，不知道如何实现呢

[解决办法]

SQL code

INSERT TB1 SELECT NID,       (SELECT MAX(FNUM) FROM TB1)+       (SELECT COUNT(1) FROM TB2 WHERE NID<=T.NID) AS  FNUM ,FDES FROM TB2 T
[解决办法]
SQL codedeclare @a table(id int, fnum int, ftest char(10))insert into @a select 1, 2, 'aa'union select 21, 3, 'bb'union select 33, 4, 'cc'declare @b table(Nid int, Findex int, fdes char(10))insert into @b select 11, 12, 'kk'union select 22, 22, 'mm'union select 33, 32, 'ss'insert into @a(id, ftest, fnum) select Nid, fdes, ROW_NUMBER() OVER (order by Findex) + (select MAX(fnum) from @a)  from @b as Bselect * from @a

热点排行

C#

一个比较头疼的sql语句，在线请问