select row_number()over(order by getdate()) 行号,* from tb
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-25 13:28:33 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -- Blog : http://blog.csdn.net/htl258 -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([name] NVARCHAR(10),[sex] INT) INSERT [tb] SELECT 'ga',1 UNION ALL SELECT 'de',0 UNION ALL SELECT 'ff',1 UNION ALL SELECT 'hh',1 UNION ALL SELECT 'kk',0 GO --SELECT * FROM [tb]-->SQL查询如下: select * from ( select row_number()over(order by getdate()) 行号,* from tb ) as t where name='ff' /* 行号 name sex -------------------- ---------- ----------- 3 ff 1(1 行受影响) */
select 行号=idd from (select idd=row_number()over(order by getdate()),* from tb)t where name=ff
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-25 13:28:33 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -- Blog : http://blog.csdn.net/htl258 -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([name] NVARCHAR(10),[sex] INT) INSERT [tb] SELECT 'ga',1 UNION ALL SELECT 'de',0 UNION ALL SELECT 'ff',1 UNION ALL SELECT 'hh',1 UNION ALL SELECT 'kk',0 GO --SELECT * FROM [tb]-->SQL2000查询如下: alter table tb add 行号 int identity go select * from tb where name='ff' /* 行号 name sex -------------------- ---------- ----------- 3 ff 1(1 行受影响) */
--多多动手,相互提升 select *,row_number() over(order by getdate()) 行号 from tb name sex 行号 ga 1 1 de 0 2 ff 1 3 hh 1 4 kk 0 5
from tb
-- Author : htl258(Tony)
-- Date : 2010-04-25 13:28:33
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[sex] INT)
INSERT [tb]
SELECT 'ga',1 UNION ALL
SELECT 'de',0 UNION ALL
SELECT 'ff',1 UNION ALL
SELECT 'hh',1 UNION ALL
SELECT 'kk',0
GO
--SELECT * FROM [tb]-->SQL查询如下:
select *
from (
select row_number()over(order by getdate()) 行号,*
from tb
) as t
where name='ff'
/*
行号 name sex
-------------------- ---------- -----------
3 ff 1(1 行受影响)
*/
from tb)t where name=ff
-- Author : htl258(Tony)
-- Date : 2010-04-25 13:28:33
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[sex] INT)
INSERT [tb]
SELECT 'ga',1 UNION ALL
SELECT 'de',0 UNION ALL
SELECT 'ff',1 UNION ALL
SELECT 'hh',1 UNION ALL
SELECT 'kk',0
GO
--SELECT * FROM [tb]-->SQL2000查询如下:
alter table tb add 行号 int identity
go
select * from tb where name='ff'
/*
行号 name sex
-------------------- ---------- -----------
3 ff 1(1 行受影响)
*/
--多多动手,相互提升
select *,row_number() over(order by getdate()) 行号
from tb
name sex 行号
ga 1 1
de 0 2
ff 1 3
hh 1 4
kk 0 5