2005及以上版本可以这样生成---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-12-24 14:24:18 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([id] int,[name] varchar(6),[ischeck] int) insert [huang] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null --------------开始查询--------------------------select ID,NAME,ROW_NUMBER()OVER(ORDER BY id)[ischeck] from [huang] ----------------结果---------------------------- /* ID NAME ischeck ----------- ------ -------------------- 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */
好像没什么比较简单的方法if object_id('[huang]') is not null drop table [huang] go create table [huang]([id] int,[name] varchar(6),[ischeck] int) insert [huang] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',nullselect ID,NAME,IDENTITY(INT,1,1) AS ischeck into #t from [huang]SELECT * FROM #T/* ID NAME ischeck ----------- ------ ----------- 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */
因为我的ID另有用途 在不能改变ID的情况下 我用Ischeck来排序 sql2000有什么办法不? if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null goselect ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1 /* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */
因为我的ID另有用途 在不能改变ID的情况下 我用Ischeck来排序 sql2000有什么办法不? if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null goselect ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1 /* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */ select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊
因为我的ID另有用途 在不能改变ID的情况下 我用Ischeck来排序 sql2000有什么办法不? if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null goselect ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1 /* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */ select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊他是造数据而已
因为我的ID另有用途 在不能改变ID的情况下 我用Ischeck来排序 sql2000有什么办法不? if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null goselect ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1 /* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */ select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊哦,上面是构造实验数据,你直接运行这个查询试试,把表的名字改成你的,还有字段: select ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1
/* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */
因为我的ID另有用途 在不能改变ID的情况下 我用Ischeck来排序 sql2000有什么办法不? if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null goselect ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1 /* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */ select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊哦,上面是构造实验数据,你直接运行这个查询试试,把表的名字改成你的,还有字段: select ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1
/* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */OK,查询出来了 可是我需要把数据库的ischeck字段更新好了啊 这该怎么弄呢?谢谢!
select ID,NAME,IDENTITY(INT,1,1) AS ischeck into #t from [huang]update huang set ischeck =a.ischeck from #t a where huang.id=a.id
因为我的ID另有用途 在不能改变ID的情况下 我用Ischeck来排序 sql2000有什么办法不? if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb] select 1,'jack',null union all select 2,'tony',null union all select 3,'amanda',null union all select 4,'xiao',null union all select 5,'ming',null goselect ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1 /* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */ select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊哦,上面是构造实验数据,你直接运行这个查询试试,把表的名字改成你的,还有字段: select ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck from [tb] t1
/* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */OK,查询出来了 可是我需要把数据库的ischeck字段更新好了啊 这该怎么弄呢?谢谢!这样就行: update tb set ischeck = (select COUNT(*) from tb t2 where tb.id >= t2.id)select * from [tb] t1
/* ID NAME ischeck 1 jack 1 2 tony 2 3 amanda 3 4 xiao 4 5 ming 5 */
如果你用他的,可以这样写: select ID,NAME, (select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck into #t from [tb] t1 update tb set ischeck =a.ischeck from #t a where tb.id=a.id
如果这句也可以,那就每次执行都执行一下这句: update tb set ischeck = (select COUNT(*) from tb t2 where tb.id >= t2.id)
问题1:DECLARE @id VARCHAR(100), @ischeck VARCHAR(100); BEGIN DECLARE c_test_main CURSOR FAST_FORWARD FOR select ID,ROW_NUMBER() over(order by id) from temp123; OPEN c_test_main; FETCH NEXT FROM c_test_main INTO @id, @ischeck; WHILE @@fetch_status = 0 BEGIN update temp123 set ischeck=@ischeck where id=@id; FETCH NEXT FROM c_test_main INTO @id, @ischeck; END; CLOSE c_test_main; DEALLOCATE c_test_main; END; go
重新排序,假设要在id=2后插入:update temp123 set ischeck=ischeck+1 where ID>2
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-24 14:24:18
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] varchar(6),[ischeck] int)
insert [huang]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
--------------开始查询--------------------------select ID,NAME,ROW_NUMBER()OVER(ORDER BY id)[ischeck]
from [huang]
----------------结果----------------------------
/*
ID NAME ischeck
----------- ------ --------------------
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
go
create table [huang]([id] int,[name] varchar(6),[ischeck] int)
insert [huang]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',nullselect ID,NAME,IDENTITY(INT,1,1) AS ischeck into #t
from [huang]SELECT * FROM #T/*
ID NAME ischeck
----------- ------ -----------
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
goselect ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
goselect ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
goselect ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊他是造数据而已
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
goselect ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊哦,上面是构造实验数据,你直接运行这个查询试试,把表的名字改成你的,还有字段:
select ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
goselect ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊哦,上面是构造实验数据,你直接运行这个查询试试,把表的名字改成你的,还有字段:
select ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/OK,查询出来了 可是我需要把数据库的ischeck字段更新好了啊 这该怎么弄呢?谢谢!
from [huang]update huang
set ischeck =a.ischeck
from #t a
where huang.id=a.id
if object_id('[tb]') is not null drop table [tb]
go create table [tb]([id] int,[name] varchar(6),[ischeck] int)insert [tb]
select 1,'jack',null union all
select 2,'tony',null union all
select 3,'amanda',null union all
select 4,'xiao',null union all
select 5,'ming',null
goselect ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
select 1,'jack',null union allselect 2,'tony',null union allselect 3,'amanda',null union allselect 4,'xiao',null union allselect 5,'ming',null 这个我是不是数据库有几条我就要都拆开来写啊哦,上面是构造实验数据,你直接运行这个查询试试,把表的名字改成你的,还有字段:
select ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck
from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/OK,查询出来了 可是我需要把数据库的ischeck字段更新好了啊 这该怎么弄呢?谢谢!这样就行:
update tb
set ischeck = (select COUNT(*) from tb t2 where tb.id >= t2.id)select * from [tb] t1
/*
ID NAME ischeck
1 jack 1
2 tony 2
3 amanda 3
4 xiao 4
5 ming 5
*/
select ID,NAME,
(select COUNT(*) from tb t2 where t1.id >= t2.id) as ischeck into #t
from [tb] t1
update tb
set ischeck =a.ischeck
from #t a
where tb.id=a.id
update tb
set ischeck = (select COUNT(*) from tb t2 where tb.id >= t2.id)
BEGIN
DECLARE c_test_main CURSOR FAST_FORWARD FOR
select ID,ROW_NUMBER() over(order by id) from temp123;
OPEN c_test_main;
FETCH NEXT FROM c_test_main INTO @id, @ischeck;
WHILE @@fetch_status = 0
BEGIN
update temp123 set ischeck=@ischeck where id=@id;
FETCH NEXT FROM c_test_main INTO @id, @ischeck;
END;
CLOSE c_test_main;
DEALLOCATE c_test_main;
END;
go