with t as( select * ,row_number()over(order by id) as px from tb1)insert into tb2(id,data) select id,data from t where px>1
--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:tb1 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tb1') AND type in (N'U')) DROP TABLE tb1 GO---->建表 create table tb1([id] int,[data] int) insert tb1 select 2,23213 union all select 3,12313 union all select 4,51531 union all select 5,12341 GO--> 查询结果 SELECT * FROM tb1--对比 ;with t as( select * ,row_number()over(order by id) as px from tb1)select id,data from t where px>1 --> 删除表格 --DROP TABLE tb1怎么不行呢?
我是sql2000 提示说'row_number' 不是可以识别的 函数名。
INSERT INTO b SELECT * FROM a WHERE id>(SELECT TOP 1 id FROM a)
INSERT INTO b SELECT * FROM a WHERE id>(SELECT TOP 1 id FROM a) 这样写可以不
nicedream啊 我觉得可行 但是有个问题就是id是个自增的标识,提示仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 中为标识列指定显式值。
insert into b select * from aDELETE FROM b WHERE ID IN (SELECT TOP 1 ID FROM b)
select id,data from tb1
where id>1---这个条件你根据实际情况写
select * ,row_number()over(order by id) as px
from tb1)insert into tb2(id,data)
select id,data from t
where px>1
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:tb1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tb1')
AND type in (N'U'))
DROP TABLE tb1
GO---->建表
create table tb1([id] int,[data] int)
insert tb1
select 2,23213 union all
select 3,12313 union all
select 4,51531 union all
select 5,12341
GO--> 查询结果
SELECT * FROM tb1--对比
;with t as(
select * ,row_number()over(order by id) as px
from tb1)select id,data from t
where px>1
--> 删除表格
--DROP TABLE tb1怎么不行呢?
SELECT * FROM a WHERE id>(SELECT TOP 1 id FROM a)
SELECT * FROM a WHERE id>(SELECT TOP 1 id FROM a)
这样写可以不
insert into b
select * from aDELETE FROM b
WHERE ID IN (SELECT TOP 1 ID FROM b)