tba有字段linecd中ADPT208|ADPT212
ADPT214
ADPT215
ADPT217|ADPT218|ADPT219
ADPT220
tbb也有字段linecd
ADPT214
ADPT215如果写
select linecd as processcd from tba where linecd not in (select linecd from tbb)
为什么这样写出来查不出数据的?预想是得到
ADPT208|ADPT212
ADPT217|ADPT218|ADPT219
ADPT220
ADPT214
ADPT215
ADPT217|ADPT218|ADPT219
ADPT220
tbb也有字段linecd
ADPT214
ADPT215如果写
select linecd as processcd from tba where linecd not in (select linecd from tbb)
为什么这样写出来查不出数据的?预想是得到
ADPT208|ADPT212
ADPT217|ADPT218|ADPT219
ADPT220
except
select linecd from tbb
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-16 13:14:27
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([linecd] varchar(23))
insert [a]
select 'ADPT208|ADPT212' union all
select 'ADPT214' union all
select 'ADPT215' union all
select 'ADPT217|ADPT218|ADPT219' union all
select 'ADPT220'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([linecd] varchar(7))
insert [b]
select 'ADPT214' union all
select 'ADPT215'
--------------开始查询--------------------------
select linecd as processcd from a where linecd not in (select linecd from b)
----------------结果----------------------------
/* processcd
-----------------------
ADPT208|ADPT212
ADPT217|ADPT218|ADPT219
ADPT220(3 行受影响)
*/
with tba(linecd) as
(
select 'ADPT208|ADPT212' union all
select 'ADPT214' union all
select 'ADPT215' union all
select 'ADPT217|ADPT218|ADPT219' union all
select 'ADPT220'
),tbb(linecd) as
(
select 'ADPT214' union all
select 'ADPT215'
)select linecd as processcd from tba where linecd not in (select linecd from tbb)
/*processcd
-----------------------
ADPT208|ADPT212
ADPT217|ADPT218|ADPT219
ADPT220(3 行受影响)
--楼主仔细看下自己的数据有没空格之类的!
这样 有影响吗?
create table [a]([linecd] varchar(500))
insert [a]
select 'ADPT208|ADPT212' union all
select 'ADPT214' union all
select 'ADPT215' union all
select 'ADPT217|ADPT218|ADPT219' union all
select 'ADPT220'
--> 测试数据:[b]
create table [b]([linecd] varchar(500))
insert [b]
select 'ADPT214' union all
select 'ADPT215'
--------------开始查询--------------------------
select linecd as processcd from a where linecd not in (select linecd from b)DROP TABLE a
DROP TABLE b 实验表明没有影响!