如何根椐列值找列名?求一条SELECT语句
col1col2col2col3col4col5col6col7
NULLxxxxxNULLNULLNULLNULLNULLNULL
我想得到列值为xxxxx的列名！谢谢
PS：该表只有一条记录。
[解决办法]
不知道这个方法是不是太笨了
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
insert into 表(col1,col2,col3,col4)
select NULL, 'xxxxx ',NULL,NULL
if exists(select 1 from 表 where col1= 'xxxxx ')
print 'col1 '
else
begin
if exists(select 1 from 表 where col2= 'xxxxx ')
print 'col2 '
else
begin
if exists(select 1 from 表 where col3= 'xxxxx ')
print 'col3 '
else
begin
if exists(select 1 from 表 where col4= 'xxxxx ')
print 'col4 '
end
end
end
[解决办法]
create table T(col1 varchar(20), col2 varchar(20), col3 varchar(20), col4 varchar(20))
go
insert T select null, 'xxxxx ', null, null
go
select * from
(
select colName= 'col1 ', col1 from T
union all
select colName= 'col2 ', col2 from T
union all
select colName= 'col3 ', col3 from T
union all
select colName= 'col4 ', col4 from T
)tmp where col1 like 'xx% '
[解决办法]
declare col cursor for select col1,col2,col3,col4,col5,col6,col7 from table1;
open col;
fetch col into :@col1,@col2,@col3,@col4,@col5,@col6,@col7;
if @col1= 'xxxxx ' print col1;
if @col2= 'xxxxx ' print col2;
if @col3= 'xxxxx ' print col3;
if @col4= 'xxxxx ' print col4;
if @col5= 'xxxxx ' print col5;
if @col6= 'xxxxx ' print col6;
if @col7= 'xxxxx ' print col7;
close col;
deallocate col;
[解决办法]
create table test(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10))
insert into test select NULL, 'xxxxx ',NULL,NULL,NULL,NULL,NULL
go
declare @v varchar(10),@sql varchar(8000)
set @v= 'xxxxx '
set @sql= ' '
select @sql=@sql+ ' union all select (case rtrim( '+name+ ') when ' ' '+@v+ ' ' ' then ' ' '+name+ ' ' ' end) from test where '+name+ ' is not null '
from syscolumns where id=object_id( 'test ')
set @sql=stuff(@sql,1,11, ' ')
exec(@sql)
/*
----
col2
*/
go
drop table test
go
[解决办法]
--借下表
create table 表(col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
insert into 表(col1,col2,col3,col4)
select NULL, 'xxxxx ',NULL,NULL
select
(select top 1 col
from(select 'col1 ' col, col1 union all select 'col2 ', col2 union all select 'col3 ', col3 union all select 'col4 ', col4)a
where col1= 'xxxxx ')
from 表
----
col2
（所影响的行数为 1 行）
[解决办法]
如果仅某个字段有值,其余全为null的话
select
isnull(case when col1 is not null then 'col1 ' end, ' ')+
isnull(case when col2 is not null then 'col2 ' end, ' ')+
isnull(case when col3 is not null then 'col3 ' end, ' ')+
isnull(case when col4 is not null then 'col4 ' end, ' ')+
isnull(case when col5 is not null then 'col5 ' end, ' ')+
isnull(case when col6 is not null then 'col6 ' end, ' ')+
isnull(case when col7 is not null then 'col7 ' end, ' ')
from table1
[解决办法]
declare @value varchar(80);
set @value= 'xxxxx '
select
case when col1=@value then 'col1 ' else ' ' end +
case when col2=@value then 'col2 ' else ' ' end +
case when col3=@value then 'col3 ' else ' ' end +
case when col4=@value then 'col4 ' else ' ' end +
case when col5=@value then 'col5 ' else ' ' end +
case when col6=@value then 'col6 ' else ' ' end +
case when col7=@value then 'col7 ' else ' ' end as 'column '
from table1
[解决办法]
--改一下子陌的，防止出现有两个值时产生NULL的情况
create table test(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10))
insert into test select NULL, 'xxxxx ',NULL, 'yyyyy ',NULL,NULL,NULL
go
declare @v varchar(10),@sql varchar(8000)
set @v= 'xxxxx '
set @sql= ' '
select @sql=@sql+ ' union all select colname=(case rtrim( '+name+ ') when ' ' '+@v+ ' ' ' then ' ' '+name+ ' ' ' end) from test where '+name+ ' is not null and rtrim( '+name+ ')= ' ' '+@v+ ' ' ' '
from syscolumns where id=object_id( 'test ')
set @sql=stuff(@sql,1,11, ' ')
exec(@sql)
/*
----
col2
*/
go
drop table test
go
[解决办法]
--还可以做成存储过程
create table test(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10))
insert into test select NULL, 'xxxxx ',NULL, 'yyyyy ',NULL,NULL,NULL
go
create proc find_colname
@value varchar(10)
as
declare @v varchar(10),@sql varchar(8000)
set @v= 'xxxxx '
set @sql= ' '
select @sql=@sql+ ' union all select colname=(case rtrim( '+name+ ') when ' ' '+@v+ ' ' ' then ' ' '+name+ ' ' ' end) from test where '+name+ ' is not null and rtrim( '+name+ ')= ' ' '+@v+ ' ' ' '
from syscolumns where id=object_id( 'test ')
set @sql=stuff(@sql,1,11, ' ')
exec(@sql)
go
exec find_colname 'xxx '
go
/*
colname
-------------
col2
*/
drop table test
drop proc find_colname
go
[解决办法]
第一步:找出 xxxxx 在第几列(例如:@col)
第二步:select name from syscolumns where id=OBJECT_ID( '表名 ') and colid=@col
[解决办法]
折腾人的想法
