select biao1.学号,biao2.姓名,biao1.分数,biao1.年份,identity(1,1) as 序号 from biao1,biao2 where biao1.年份 = '2004' and biao 1.学号 = biao2.学号
select a.学号,b.姓名,a.分数,a.年份,identity(1,1) as 序号 into #a from 表1 a,表2 b where a.学号=b.学号 and 年份='2004' order by 学号select * from #a order by 序号
to yesterday2000(一笑而过) : "into #a" 请问表名前面加个"#"是什么意思呢?是不是表示是临时表呢??? 如果不用into 语句,能不能实现我说的递增为1排列呢?
#是一个临时表如果不用into语句的话,则不能使用IDENTITY重建标识项Declare @table_1 table (学号 varchar(10),分数 tinyint,年份 varchar(4)) Insert into @table_1 Select '001',100,'2003' Union All Select '001',100,'2004' Union All Select '002',100,'2002' Union All Select '002',100,'2003' Union All Select '002',100,'2004' Union All Select '003',100,'2003' Union All Select '003',100,'2004'Declare @table_2 table (学号 varchar(10),名字 varchar(10)) Insert into @table_2 Select '001','张三' Union All Select '002','李四' Union All Select '003','王五'Select A.学号,B.名字,A.分数,A.年份,IDENTITY(int,1,1) as '序号' into #A From @table_1 A join @table_2 B on A.学号=B.学号 Where A.年份='2004' GROUP BY A.学号,B.名字,A.分数,A.年份Select * from #A
select 表1.学号,表2.姓名,表1.分数,表1.年份,Identity(1,1) as 序号 from 表1,表2 where 表1.学号 = 表2.学号 and 表1.年份 = '2004'
select biao1.学号,biao2.姓名,biao1.分数,biao1.年份,identity(1,1) as 序号 from biao1,biao2 where biao1.年份 = '2004' and biao 1.学号 = biao2.学号
from biao1,biao2
where biao1.年份 = '2004' and biao 1.学号 = biao2.学号
into #a
from 表1 a,表2 b
where a.学号=b.学号 and 年份='2004'
order by 学号select * from #a order by 序号
"into #a"
请问表名前面加个"#"是什么意思呢?是不是表示是临时表呢???
如果不用into 语句,能不能实现我说的递增为1排列呢?
Insert into @table_1 Select '001',100,'2003'
Union All Select '001',100,'2004'
Union All Select '002',100,'2002'
Union All Select '002',100,'2003'
Union All Select '002',100,'2004'
Union All Select '003',100,'2003'
Union All Select '003',100,'2004'Declare @table_2 table (学号 varchar(10),名字 varchar(10))
Insert into @table_2 Select '001','张三'
Union All Select '002','李四'
Union All Select '003','王五'Select A.学号,B.名字,A.分数,A.年份,IDENTITY(int,1,1) as '序号' into #A From @table_1 A join @table_2 B on A.学号=B.学号 Where A.年份='2004' GROUP BY A.学号,B.名字,A.分数,A.年份Select * from #A
from 表1,表2
where 表1.学号 = 表2.学号 and 表1.年份 = '2004'
from biao1,biao2
where biao1.年份 = '2004' and biao 1.学号 = biao2.学号