笨人求语句

[ruhong]

表a

a1 a2 a3 a4 a5 a6 ....a20
1 2 6 7 9......
....

求a1或者a2.......a20中，某一列的值=1的行，
求简洁的写法。

[giftzheng]

select * from a where cast(a1 as varchar(100))+','+cast(a2 as varchar(100))+',' +...+cast(a2 as varchar(100)) like '%1,%'

[Mr_Nice]

select * 
from TB
where a1=1 or a2=1 or a3=1 or a4=1 or a5=1 or a6=1
or a7=1 or a8=1 or a9=1 or a10=1 or a11=1 or a12=1
or a13=1 or a14=1 or a15=1 or a16=1 or a17=1 or a18=1 
or a19=1 or a20=1

最朴素的写法，感觉还挺简洁的哦。

[發糞塗牆]

如果是2008的话，先创建这个存储过程：

	--创建临时表存放结果
	create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
	declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
	if @type=1 
	begin
		declare curTable cursor fast_forward
		for 
			select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
			where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99) AND o.name=@tbname
 	end
	else
	begin 
		declare curTable cursor fast_forward
		for 
		select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
			where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122) AND o.name=@tbname
END
	open curtable
	fetch next from curtable into @tbl,@col
	while @@FETCH_STATUS=0
	begin
		set @sql='if exists (select * from '+@tbl+' where '
		if @type=1
		begin
			set @sql += @col + ' like ''%'+@str +'%'')'
		end
		else 
		begin
			set @sql +=@col + ' in ('+@str+'))'
		end

		set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
		--print @sql
		exec (@sql)
		fetch next from curtable into @tbl,@col
	end
	close curtable 
	deallocate curtable
	select * from #tbl

然后执行： exec spFind_Column_In_DB 2,1,'输入你的表名'

[ruhong]

引用 2 楼 OrchidCat 的回复:

select * 
from TB
where a1=1 or a2=1 or a3=1 or a4=1 or a5=1 or a6=1
or a7=1 or a8=1 or a9=1 or a10=1 or a11=1 or a12=1
or a13=1 or a14=1 or a15=1 or a16=1 or a17=1 or a18=1 
or a19=1 or a20=1

最朴素的写法，感觉还挺简洁的哦。

也是。

[ruhong]

引用 3 楼 DBA_Huangzj 的回复:

如果是2008的话，先创建这个存储过程：

	--创建临时表存放结果
	create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
	declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
	if @type=1 
	begin
		declare curTable cursor fast_forward
		for 
			select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
			where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99) AND o.name=@tbname
 	end
	else
	begin 
		declare curTable cursor fast_forward
		for 
		select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
			where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122) AND o.name=@tbname
END
	open curtable
	fetch next from curtable into @tbl,@col
	while @@FETCH_STATUS=0
	begin
		set @sql='if exists (select * from '+@tbl+' where '
		if @type=1
		begin
			set @sql += @col + ' like ''%'+@str +'%'')'
		end
		else 
		begin
			set @sql +=@col + ' in ('+@str+'))'
		end

		set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
		--print @sql
		exec (@sql)
		fetch next from curtable into @tbl,@col
	end
	close curtable 
	deallocate curtable
	select * from #tbl

然后执行： exec spFind_Column_In_DB 2,1,'输入你的表名'

版主很强大，可是我昏了。

[發糞塗牆]

除了表名之外，你不需要改动任何东西，除非你的不是2008

[ruhong]

引用 6 楼 DBA_Huangzj 的回复:

除了表名之外，你不需要改动任何东西，除非你的不是2008

辛苦了，是2008，可是我用的是sql clr 创建存储过程。

[發糞塗牆]

......不会CLR哦