比如,我表名为student,我想得到第二行第二列的值,那select语句应该是 select * from student where.... where往后的条件怎么写?
SQL中有第二列的概念,没有第二行的概念.
create table test(col1 int,col5 int,col6 int) insert into test select 1,2,3 union all select 4,5,6 union all select 7,8,9--例如要第三行第二列的值declare @hang int set @hang=3 declare @lie int set @lie=2declare @liename varchar(50) ;with m as ( select name,lie=row_number() over (order by getdate()) from syscolumns where id=object_id('test')) select @liename=name from m where lie=@lie select row_number() over (order by getdate()) as hang,* into #t from test declare @sql varchar(8000) set @sql='select '+@liename+' from #t where hang='+ltrim(@hang)exec(@sql) drop table #t /* col5 ----------- 8 */
create proc GetValueByPos ( @tablename varchar(50),--表名 @hang int,--行号 @lie int --列号 ) as begin declare @liename varchar(50) declare @sql varchar(8000) ;with m as ( select name,lie=row_number() over (order by getdate()) from syscolumns where id=object_id(@tablename) ) select @liename=name from m where lie=@lie set @sql= 'select row_number() over (order by getdate()) as hang,* into #t from '+@tablename +char(13)+'select '+@liename+' from #t where hang='+ltrim(@hang) +char(13)+'drop table #t' exec(@sql) endexec GetValueByPos 'test',2,3/* col6 ----------- 6 */我封装成存储过程了
select * from tb where ...
比如,我表名为student,我想得到第二行第二列的值,那select语句应该是
select * from student where....
where往后的条件怎么写?
create table test(col1 int,col5 int,col6 int)
insert into test
select 1,2,3 union all
select 4,5,6 union all
select 7,8,9--例如要第三行第二列的值declare @hang int set @hang=3
declare @lie int set @lie=2declare @liename varchar(50)
;with m as (
select name,lie=row_number() over (order by getdate())
from syscolumns where id=object_id('test'))
select @liename=name from m where lie=@lie
select row_number() over (order by getdate()) as hang,* into #t from test
declare @sql varchar(8000)
set @sql='select '+@liename+' from #t where hang='+ltrim(@hang)exec(@sql)
drop table #t
/*
col5
-----------
8
*/
create proc GetValueByPos
(
@tablename varchar(50),--表名
@hang int,--行号
@lie int --列号
)
as
begin
declare @liename varchar(50)
declare @sql varchar(8000)
;with m as
(
select name,lie=row_number() over (order by getdate())
from syscolumns where id=object_id(@tablename)
)
select @liename=name from m where lie=@lie
set @sql=
'select row_number() over (order by getdate()) as hang,* into #t from '+@tablename
+char(13)+'select '+@liename+' from #t where hang='+ltrim(@hang)
+char(13)+'drop table #t'
exec(@sql)
endexec GetValueByPos 'test',2,3/*
col6
-----------
6
*/我封装成存储过程了