INSERT tab_mingxi (xh,XMID,bjbh)
SELECT xh=1+isnull((select count(*)
from jianguan.dbo.tab_baojianmingxi a
where tab_test.bjbh<a.bjbh ),0),
XMID,bjbh
from tab_test
SELECT xh=1+isnull((select count(*)
from jianguan.dbo.tab_baojianmingxi a
where tab_test.bjbh<a.bjbh ),0),
XMID,bjbh
from tab_test
SELECT xh=1+isnull((select count(*) from tab_mingxi a where bjbh<a.bjbh ),0),XMID,bjbh from tab_test
不好意思,问题上面有些错误,我原来出错的语句应该是这样的
现在改为:
INSERT tab_mingxi (xh,XMID,bjbh)
SELECT xh=1+isnull((select count(*) from tab_mingxi a where tab_test.bjbh<a.bjbh ),0),XMID,bjbh from tab_test
序号还是都为1
SELECT xh=1+isnull((select count(*) from tab_test a
where a.bjbh<b.bjbh ),0),XMID,bjbh from tab_test b
SELECT xh=(select count(*) from tab_test where bjbh<= A.bjbh )
,XMID,bjbh from tab_test as A
INTO #NewTable
FROM tab_test b
insert into tab_mingxi
select * from #NewTable如果原tab_mingxi 沒有數據,可以DROP 它
然後直接用tab_mingxi 代替上面的#NewTable即可
SELECT xh=1+isnull((select count(*) from jianguan.dbo.tab_baojianmingxi where bjbh<a.bjbh ),0),XMID,bjbh from tab_test a