请问,如何通过字段位置查询
比如:
table字段有:字段a,字段b,字段c,
那我怎么能够用这个字段的位置来查
比如;select [0],[1],[2] from table
[解决办法]
单纯的位置只能在order by中用,比如order by 1,3就是按照数据集(不一定是表)的第一、三列来排,如果你非要那样查,先说说你的理由
[解决办法]
create table table1(indexvalue int)
insert into table1
select 0 union all
select 1 union all
select 2
create table table2(字段a int,字段b int,字段c int)
insert into table2
select 1,2,3 union all
select 10,20,30
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+rtrim(b.name)+']'
from sys.tables a
inner join sys.columns b on a.object_id=b.object_id
inner join table1 c on b.column_id=c.indexvalue+1
where a.name='table2'
select @tsql='select '+@tsql+' from table2'
exec(@tsql)
/*
字段a 字段b 字段c
----------- ----------- -----------
1 2 3
10 20 30
(2 row(s) affected)
*/
create table table1
(a1 int,a2 int,a3 int,a4 int,a5 int)
insert into table1
select 1,2,4,3,5
create table tb2
(A varchar(10),B varchar(5),C varchar(10))
insert into tb2
select '20120101','1','' union all
select '20030303','4',''
-- 更新
with t as
(select right(c,1) 'd',v
from table1 a
unpivot(v for c in([a1],[a2],[a3],[a4],[a5])) u
)
update tb2
set C=(select v from t where t.d=B)
-- 结果
select * from tb2
/*
A B C
---------- ----- ----------
20120101 1 1
20030303 4 3
(2 row(s) affected)
*/