MS-SQL 数据查询的问题？

[hwj383]

有一个表
列A , B
数据
a1,b1
a2,b2
a3,b3
a4,b4

现在要求输出

我自已加的列 A B
1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4

也就是说在第行前面加上一个自增的列 int i = 0; ++i;

[greymouseyu]

SELECT id=ROW_NUMBER() over (order by A)
,[A]
,[B]
FROM [tb]

[claro]

[Quote=引用 5 楼 greymouseyu 的回复:]
SELECT id=ROW_NUMBER() over (order by A)
,[A]
,[B]
FROM [tb]
[/Quote]

[halhalhal]

简单的办法：
右键点表名，选择“设计”，新增一列，此列不允许空，并且自增长。保存后此列的数据就是楼主要求的了

[百年树人]

select id=identity(int,1,1),* into # from tb

select * from #

drop table #

[SQL77]

[Quote=引用 7 楼 josy 的回复:]
SQL codeselect id=identity(int,1,1),* into # from tb

select * from #

drop table #
[/Quote]

学习

[ws_hgo]

--(1)用临时表

select id=identity(int,1,1),* into #1 from tb
select * from #1

--(2)用row_number()

select * from
(
  select *,row_number(order by A) rank from tb
) TB
order by rank 

[lg3605119]

select id= row_number() over(order by getdate()),* from Table 

[Andy__Huang]

select id=(select count(1) from tb b where b.A<=a.A),A,B
from tb a

[Andy__Huang]

create table #tb(A varchar(10),B varchar(10))
insert into #tb values('a1','b1')
insert into #tb values('a2','b2')
insert into #tb values('a3','b3')
insert into #tb values('a4','b4')

select id=(select count(1) from #tb b where b.A <=a.A),A,B
from #tb a

id A B
-------------------------------
1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4

[Stephen_Kang]

create table tb(A varchar(10),B varchar(10)) //最好分为两步创建表并包含其中的列, //其中tb为表名
--举例创建表
(
id int identity(1,1) not null ,
A varchar(50) not null,
B varchar(50) not null
)
go //批处理语句

--创建表的约束
alter table tb
add constraint PK_id primary key (id)
go

insert into tb（列名1，列名2) values('a1','b1')
insert into tb（列名1，列名2) values('a2','b2')
insert into tb（列名1，列名2) values('a3','b3')
insert into tb （列名1，列名2) values('a4','b4')

select id as 我自己定义的列 ,A,B from tb

我自己定义的列 A B
-------------------------------
1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4


我也学习软件开发的。。10152QQ70553

[叶子]

select row_number() over(order by A) as id,* from table

[热学沸腾56]

select row_number() over(order by A) as id,* from table

[長胸為富]

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = N'tbl') DROP TABLE tbl
Create table tbl(A VARCHAR(5),B VARCHAR(6))
INSERT INTO tbl
SELECT 'a1','b1' UNION ALL 
SELECT 'a2','b2' UNION ALL 
SELECT 'a3','b3' UNION ALL 
SELECT 'a4','b4'

SELECT * FROM tbl
/*	 A   B
	a1	b1
	a2	b2
	a3	b3
	a4	b4
*/

select A,B into #t from tbl

delete from tbl

alter table tbl add  ID int identity(1,1)

INSERT INTO tbl(A,B) SELECT A,B FROM #t
DROP TABLE #t
SELECT ID,A,B FROM tbl
/*
1	a1	b1
2	a2	b2
3	a3	b3
4	a4	b4
*/

[等待戈多12]

[Quote=引用 12 楼 halhalhal 的回复:]
简单的办法：
右键点表名，选择“设计”，新增一列，此列不允许空，并且自增长。保存后此列的数据就是楼主要求的了
[/Quote]
增量为1

[zhaodalong]

create table t_table(
A varchar(20),
B varchar(20)
)
go
insert into t_table values('a1','b1')
insert into t_table values('a2','b2')
insert into t_table values('a3','b3')
insert into t_table values('a4','b4')
select * from t_table


select '你所要添加的列名'=row_number() over(order by getdate()), * from t_table

[zhaodalong]

楼主是不是只想在表里加一个列?
直接alter table t_table add 你所需要加的列 int identity(1,1)

[mybelta2]

select identity(int ,1,1) as id,A,B into tbA from tbB

[zoom8]

--不用临时表
select id=(select count(1) from tb b where b.A <= a.A),* from tb a
--用临时表
select id=identity(int,1,1),* into # from tb
select * from #
drop table #

[rhq12345]

sql2005

select id= row_number() over(order by getdate()),* from Table 

sql2000

select id=identity(int,1,1) as id,*  from tb

[幸运的意外]

select id=(select count(1) from tb a where a.A <= b.A),A,B
from tb b