如果把没数据理解为某些列为空,可以这么写: select * from 表 where 列名 is not null
比如说一条数据 id fname lname age ....... 1 fu null 23 ........ 后面的省略号 代表 很多字段 和对应的数据 这是一个表中的,现在我要输出 的结果是 id fname age ... 1 fu 23 ... 数据中部位null的字段和对应的值
/*运行环境 SQLSERVER2005*/ -- 构造数据create table #T1 ( a int null, b int null, c int null, d int null, e int null, f int null, g int null, h int null, i int null, j int null )insert into #T1 select 1,null,null,null,null,null,null,null,null,null insert into #T1 select null,2,null,null,null,null,null,null,null,null insert into #T1 select null,null,3,null,null,null,null,null,null,null insert into #T1 select null,null,null,4,null,null,null,null,null,null insert into #T1 select null,null,null,null,5,null,null,null,null,null insert into #T1 select null,null,null,null,null,6,null,null,null,null insert into #T1 select null,null,null,null,null,null,7,null,null,null insert into #T1 select null,null,null,null,null,null,null,8,null,null insert into #T1 select null,null,null,null,null,null,null,null,9,null insert into #T1 select null,null,null,null,null,null,null,null,null,10declare @tbname varchar(100), -- 表名 @row int, -- 行数 @column int, -- 列数 @data varchar(100) -- 返回值-- 手动为变量赋值 --**********-- select @tbname = '#T1' select @row = 1 select @column = 1 --**********---- 判断行列值是否超出表范围 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)while @column<=10 begin 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 print @data if @data is not null begin set @column=@column+1 end else break end
select * from 表 where 列名 is not null
id fname lname age .......
1 fu null 23 ........
后面的省略号 代表 很多字段 和对应的数据 这是一个表中的,现在我要输出
的结果是
id fname age ...
1 fu 23 ...
数据中部位null的字段和对应的值
-- 构造数据create table #T1
( a int null,
b int null,
c int null,
d int null,
e int null,
f int null,
g int null,
h int null,
i int null,
j int null
)insert into #T1 select 1,null,null,null,null,null,null,null,null,null
insert into #T1 select null,2,null,null,null,null,null,null,null,null
insert into #T1 select null,null,3,null,null,null,null,null,null,null
insert into #T1 select null,null,null,4,null,null,null,null,null,null
insert into #T1 select null,null,null,null,5,null,null,null,null,null
insert into #T1 select null,null,null,null,null,6,null,null,null,null
insert into #T1 select null,null,null,null,null,null,7,null,null,null
insert into #T1 select null,null,null,null,null,null,null,8,null,null
insert into #T1 select null,null,null,null,null,null,null,null,9,null
insert into #T1 select null,null,null,null,null,null,null,null,null,10declare @tbname varchar(100), -- 表名
@row int, -- 行数
@column int, -- 列数
@data varchar(100) -- 返回值-- 手动为变量赋值
--**********--
select @tbname = '#T1'
select @row = 1
select @column = 1
--**********---- 判断行列值是否超出表范围
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)while @column<=10
begin
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
print @data
if @data is not null
begin
set @column=@column+1
end
else
break
end