出来了! --建表 ------------------- create table table1 (ID varchar(5) not null, num int , A1 varchar(3), A2 varchar(3), A3 varchar(3), A4 varchar(3), A5 varchar(3), A6 varchar(3), A7 varchar(3), A8 varchar(3), A9 varchar(3), ) -----------------------------插入数据 --------------------------- insert into table1 select '001','1','1','','','','','','','','' union select '001','2','','1','','','','','','','' union select '002','4','','','','4','','','','','' union select '003','3','','2','','','','','','','' -----------------------------语句 ---------------------------- select t3.* from table1 t3, (select id,num from (select id ,num,(case a1 when '' then '*' else '#'end) +(case a2 when '' then '*' else '#'end) +(case a3 when '' then '*' else '#'end) +(case a4 when '' then '*' else '#'end) +(case a5 when '' then '*' else '#'end) +(case a6 when '' then '*' else '#'end) +(case a7 when '' then '*' else '#'end) +(case a8 when '' then '*' else '#'end) +(case a9 when '' then '*' else '#'end) as mystr from table1 )t1 where right(left(mystr,num),1) = '*' )t2 where t3.id = t2.id and t3.num = t2.num ------------------------------执行结果 ----------------------------- 003 3 2 -----------------------------
create table A ( 编号 varchar(10), 序号 int, A1 int, A2 int, A3 int, A4 int, A5 int, A6 int, A7 int, A8 int, A9 int ) insert A(编号,序号,A1,A2,A3,A4) select '001',1,1,null,null,null union select '001',2,null,1,null,null union select '002',4,null,null,null,4 union select '003',3,null,2,null,null --查询 select identity(int,1,1) id,* into # from A create table #r ( 编号 varchar(10), 序号 int ) declare @sql varchar(8000) ,@i int ,@ int select @sql='',@i=1while @i<=(select max(id) from #) begin select @=序号 from # where id=@i select @sql=name from syscolumns where id=object_id('A') and colorder=@+2 select @sql='if exists(select 1 from # where id='+convert(varchar,@i)+' and '+@sql+' is null)' +'insert #r select 编号,序号 from # where id='+convert(varchar,@i) exec(@sql) set @i=@i+1 endselect * from #r--删除测试环境 drop table #,#r drop table A--结果 /* 编号 序号 ---------- ----------- 003 3(所影响的行数为 1 行) */
--建表
-------------------
create table table1
(ID varchar(5) not null,
num int ,
A1 varchar(3),
A2 varchar(3),
A3 varchar(3),
A4 varchar(3),
A5 varchar(3),
A6 varchar(3),
A7 varchar(3),
A8 varchar(3),
A9 varchar(3),
)
-----------------------------插入数据
---------------------------
insert into table1
select '001','1','1','','','','','','','',''
union
select '001','2','','1','','','','','','',''
union
select '002','4','','','','4','','','','',''
union
select '003','3','','2','','','','','','',''
-----------------------------语句
----------------------------
select t3.* from table1 t3,
(select id,num
from
(select id ,num,(case a1 when '' then '*' else '#'end)
+(case a2 when '' then '*' else '#'end)
+(case a3 when '' then '*' else '#'end)
+(case a4 when '' then '*' else '#'end)
+(case a5 when '' then '*' else '#'end)
+(case a6 when '' then '*' else '#'end)
+(case a7 when '' then '*' else '#'end)
+(case a8 when '' then '*' else '#'end)
+(case a9 when '' then '*' else '#'end)
as mystr
from table1
)t1 where right(left(mystr,num),1) = '*'
)t2
where t3.id = t2.id and t3.num = t2.num
------------------------------执行结果
-----------------------------
003 3 2
-----------------------------
(
编号 varchar(10),
序号 int,
A1 int,
A2 int,
A3 int,
A4 int,
A5 int,
A6 int,
A7 int,
A8 int,
A9 int
)
insert A(编号,序号,A1,A2,A3,A4)
select '001',1,1,null,null,null union
select '001',2,null,1,null,null union
select '002',4,null,null,null,4 union
select '003',3,null,2,null,null --查询
select identity(int,1,1) id,* into # from A
create table #r
(
编号 varchar(10),
序号 int
)
declare @sql varchar(8000)
,@i int
,@ int
select @sql='',@i=1while @i<=(select max(id) from #)
begin
select @=序号 from # where id=@i
select @sql=name from syscolumns where id=object_id('A') and colorder=@+2
select @sql='if exists(select 1 from # where id='+convert(varchar,@i)+' and '+@sql+' is null)'
+'insert #r select 编号,序号 from # where id='+convert(varchar,@i)
exec(@sql)
set @i=@i+1
endselect * from #r--删除测试环境
drop table #,#r
drop table A--结果
/*
编号 序号
---------- -----------
003 3(所影响的行数为 1 行)
*/
如果用动态SQL就麻烦了,因为这只是一个存储过程中的一小段程序。我要把这个结果集放到一个临时表里,后面还有大量的处理工作
在这里再次谢谢各位的帮忙。