有一组确定的值‘aaa’,‘bbb’,‘ccc’,‘ddd’
我想判断他不在另一张表(table)的一列(Filed)里面,如何用一条SQL语句搞定,
之前很傻的写成:
select (‘aaa’,‘bbb’,‘ccc’,‘ddd') not in select Filed from table
我想判断他不在另一张表(table)的一列(Filed)里面,如何用一条SQL语句搞定,
之前很傻的写成:
select (‘aaa’,‘bbb’,‘ccc’,‘ddd') not in select Filed from table
select count(1from [table] where filed not in('aaa’,‘bbb’,‘ccc’,‘ddd') ?)
select count(1) from [table] where filed not in('aaa’,‘bbb’,‘ccc’,‘ddd') ?
from
(
select 'aaa' as code
union all select 'bbb'
union all select 'ccc'
union all select 'ddd'
) t
where code not in (select Filed from table )
Create table table1
(
Filed varchar(8000)
)
insert into table1 values ('aaa')
insert into table1 values ('bbb')
insert into table1 values ('ccc')
insert into table1 values ('ddd')
insert into table1 values ('eee')
insert into table1 values ('fff')select Filed from table1
where Filed not in ('aaa','bbb','ccc','ddd')
(SELECT 'AAA' AS A
UNION ALL
SELECT 'BBB'
UNION ALL
SELECT 'CCC'
UNION ALL
SELECT 'DDD')AS T WHERE A
NOT IN (select Filed from table WHERE Filed IS NOT NULL)
SET @S='AAA,BBB,CCC,DDD'
SET @S='SELECT A='''+REPLACE(@S,',',''' UNION ALL SELECT ''')+''''
PRINT('SELECT A FROM ('+@S+')AS T WHERE A NOT IN (SELECT FILED FROM TBB WHERE FILED IS NOT NULL)')
/
where file not in ('aa','bb','cc')
from (select 'aaa' as Col union select 'bbb' union select 'ccc')t
where ....
我看这种情况一条sql是不是完成不了啊!
if object_id('[table1]') IS NOT NULL DROP TABLE table1;
if object_id('[table2]') IS NOT NULL DROP TABLE table2;Create table table1
(
Filed varchar(8000)
)
insert into table1 values ('aaa')
insert into table1 values ('bbb')
insert into table1 values ('ccc')
insert into table1 values ('ddd')
insert into table1 values ('eee')
insert into table1 values ('fff')Create table table2
(
Filed varchar(8000)
)
insert into table2 values ('aaa')
insert into table2 values ('bbb')
insert into table2 values ('ccc')
insert into table2 values ('ddd')
insert into table2 values ('eee')
insert into table2 values ('fff')
insert into table2 values ('ggg')
insert into table2 values ('hhh')
insert into table2 values ('iii')
insert into table2 values ('jjj')
insert into table2 values ('kkk')
select Filed from table2
where Filed not in (
SELECT Filed FROM table1
)呵呵,笨方法,把值写到数据库里面算了。
‘aaa’,‘bbb’,‘ccc’,‘ddd’
既然你这组值可能很多,那建议插入到一张表中吧。然后判断这组值不在另一张表(table)的一列(Filed)里面