--方法比较原始,还必须有一个自增列,不知道是不是符合楼主要求 --测试环境if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tb] GOCREATE TABLE [dbo].[tb] ( [id] [int] IDENTITY (1, 1) NOT NULL , [q1] [int] NULL , [q2] [int] NULL , [q3] [int] NULL , [q4] [int] NULL , [q5] [int] NULL ) ON [PRIMARY] GO insert into tbselect '1','3','6','5','4' union all select '2','3','5','4','1' union all select '-1','2','1','-8','1' union all select '5','0','-1','1','5' union all select '5','0','2','5','7' union all select '-4','-9','-4','-5','1' union all select '-8','-4','-2','-2','-3' union all select '-3','-2','-6','-2','-3'go--脚本declare @str varchar(7000), @strtemp varchar(300), @tablename varchar(300), @identitycol varchar(300)set @tablename ='tb' --表名,需要根据情况修改set @identitycol='id' --自增列名,需要根据情况修改if @tablename is not nullselect @str='sign('+name+')' from syscolumns where id=object_id(@tablename) and name like 'q%' order by name descset @strtemp='in ('+@str+',0)'set @str=@str+'in ('+@str+',0)'select @str=@str +' and sign('+name+')'+@strtemp from syscolumns where id=object_id(@tablename) and name like 'q%' --需要根据情况适当加条件,如果某列不是 int ,会报错的set @str ='select * from tb where '+@identitycol+' not in ('+ 'select '+@identitycol+' from '+@tablename+' where '+@str+')'exec (@str) /* 3 -1 2 1 -8 1 4 5 0 -1 1 5 6 -4 -9 -4 -5 1*/
--测试环境if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tb]
GOCREATE TABLE [dbo].[tb] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [int] NULL ,
[q2] [int] NULL ,
[q3] [int] NULL ,
[q4] [int] NULL ,
[q5] [int] NULL
) ON [PRIMARY]
GO
insert into tbselect '1','3','6','5','4'
union all
select '2','3','5','4','1'
union all
select '-1','2','1','-8','1'
union all
select '5','0','-1','1','5'
union all
select '5','0','2','5','7'
union all
select '-4','-9','-4','-5','1'
union all
select '-8','-4','-2','-2','-3'
union all
select '-3','-2','-6','-2','-3'go--脚本declare @str varchar(7000),
@strtemp varchar(300),
@tablename varchar(300),
@identitycol varchar(300)set @tablename ='tb' --表名,需要根据情况修改set @identitycol='id' --自增列名,需要根据情况修改if @tablename is not nullselect @str='sign('+name+')'
from syscolumns
where id=object_id(@tablename)
and name like 'q%'
order by name descset @strtemp='in ('+@str+',0)'set @str=@str+'in ('+@str+',0)'select @str=@str +' and sign('+name+')'+@strtemp
from syscolumns
where id=object_id(@tablename)
and name like 'q%' --需要根据情况适当加条件,如果某列不是 int ,会报错的set @str ='select * from tb where '+@identitycol+' not
in ('+ 'select '+@identitycol+' from '+@tablename+' where '+@str+')'exec (@str)
/*
3 -1 2 1 -8 1
4 5 0 -1 1 5
6 -4 -9 -4 -5 1*/