select row_number() over (order by getdate()) id,a,b from @t where id ='' --选择行 and a = '' --选择列 and b = '' --选择列
declare @t table([a][int],[b][int]) insert into @t select 1,2select [b] from (select *,id=row_number() over(order by a) from @t) t where id=1 第i行 --语句中的id 就是第几行 第j列--列明就是那一列,没有所谓的第几列
with cte as (select row_number() over (order by getdate()) TID,* from [table])select col --第J个字段 from cte where TID=i --第I行
你的问题很怪! 想动态查询是吧! 第i行可以通过row_number函数实现。 至于第j列,那可要查询系统表了。declare @tbname varchar(50) declare @j int set @j=1 set @tbname='tb'select a.name from syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' --where d.name='tb' and a.colid=1 where d.name=@tbname and a.colid=j 然后动态了!
/*运行环境 SQLSERVER2005*/ -- 构造数据 create table #T1 ( a int, b int, c int, d int )insert into #T1 select 1,5,3,2 insert into #T1 select 2,4,12,12 insert into #T1 select 1,30,12,34 insert into #T1 select 45,23,9,2 insert into #T1 select 16,5,89,2 insert into #T1 select 21,5,32,2declare @tbname varchar(100), -- 表名 @row int, -- 行数 @column int, -- 列数 @data varchar(100) -- 返回值-- 手动为变量赋值 --**********-- select @tbname = '#T1' select @row = 1 select @column = 2 --**********---- 判断行列值是否超出表范围 if ( @row > ( select count(1) from #T1 ) ) begin print '输入行大于表最大行数' end select @tbname = 'tempdb..' + @tbname if ( @column > ( select max(column_id) from tempdb.sys.columns where object_id = object_id(@tbname) ) ) begin print '输入列大于表最大列数' end-- 定义动态SQL declare @sql nvarchar(2000), @parm nvarchar(500), @col_name varchar(100)select @col_name = name from tempdb.sys.columns where object_id = object_id(@tbname) and column_id = @columnselect @parm = '@val varchar(10) output'select @sql = ' select @val = ' + @col_name + ' from ( select *, row_number()over(order by getdate()) as ordr from ' + @tbname + ' ) a where ordr = ' + cast(@row as varchar) exec sp_executesql @sql, @parm, @data output select @data
from @t
where id ='' --选择行
and a = '' --选择列
and b = '' --选择列
insert into @t select 1,2select [b] from (select *,id=row_number() over(order by a) from @t) t where id=1
第i行 --语句中的id 就是第几行
第j列--列明就是那一列,没有所谓的第几列
with cte as (select row_number() over (order by getdate()) TID,* from [table])select col --第J个字段
from cte
where TID=i --第I行
想动态查询是吧!
第i行可以通过row_number函数实现。
至于第j列,那可要查询系统表了。declare @tbname varchar(50)
declare @j int
set @j=1
set @tbname='tb'select a.name from
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
--where d.name='tb' and a.colid=1
where d.name=@tbname and a.colid=j
然后动态了!
/*运行环境 SQLSERVER2005*/
-- 构造数据
create table #T1
( a int,
b int,
c int,
d int
)insert into #T1 select 1,5,3,2
insert into #T1 select 2,4,12,12
insert into #T1 select 1,30,12,34
insert into #T1 select 45,23,9,2
insert into #T1 select 16,5,89,2
insert into #T1 select 21,5,32,2declare @tbname varchar(100), -- 表名
@row int, -- 行数
@column int, -- 列数
@data varchar(100) -- 返回值-- 手动为变量赋值
--**********--
select @tbname = '#T1'
select @row = 1
select @column = 2
--**********---- 判断行列值是否超出表范围
if ( @row > ( select count(1) from #T1 ) )
begin
print '输入行大于表最大行数'
end
select @tbname = 'tempdb..' + @tbname
if ( @column > ( select max(column_id)
from tempdb.sys.columns
where object_id = object_id(@tbname) ) )
begin
print '输入列大于表最大列数'
end-- 定义动态SQL
declare @sql nvarchar(2000),
@parm nvarchar(500),
@col_name varchar(100)select @col_name = name
from tempdb.sys.columns
where object_id = object_id(@tbname)
and column_id = @columnselect @parm = '@val varchar(10) output'select @sql = ' select @val = ' + @col_name +
' from ( select *,
row_number()over(order by getdate()) as ordr
from ' + @tbname +
' ) a
where ordr = ' + cast(@row as varchar)
exec sp_executesql @sql,
@parm,
@data output
select @data