--可用下面方法实现SELECT * FROM tblTempEACompany WHERE
cast(NTCLastName as varbinary) NOT IN
(SELECT cast(ContactName as varbinary) FROM tblContact)
cast(NTCLastName as varbinary) NOT IN
(SELECT cast(ContactName as varbinary) FROM tblContact)
解决方案 »
- 求一个文章ID关联到多个标签的存储过程
- 请问各位兄弟,如何快速地大量地导入数据到数据库里面?
- guest用户如何启动sqlerver服务端
- 初学触发器,写的触发器有问题,请教大家...
- 急急急!把EXCEL中的数据导入到sql表中。我这怎么不好用啊。
- 有一表与表关联问题求教
- byte[] byteArray = new byte[255]{};字节数组存数据库用什么类型存?
- sql server 200 和sql server 7的还原脚本问题
- 关于数据添加的问题!
- 如何写判断每一位为数字的约束?
- *****大侠求救,通过SQL Query如何查找"TCP/IP"端口号???????
- set rowcount 是什么意思
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContact]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContact]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempEACompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTempEACompany]
GOCREATE TABLE [dbo].[tblContact] (
[ContactName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[tblTempEACompany] (
[NTCLastName] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOinsert into tblTempEACompany(NTCLastName) values('1')
insert into tblTempEACompany(NTCLastName) values('2')
insert into tblTempEACompany(NTCLastName) values('中文')
insert into tblTempEACompany(NTCLastName) values('english')insert into tblContact(ContactName) values('1')
insert into tblContact(ContactName) values('2')
insert into tblContact(ContactName) values('3')
insert into tblContact(ContactName) values('4')
--测试
SELECT NTCLastName FROM tblTempEACompany WHERE
NTCLastName not IN (SELECT ContactName FROM tblContact)
--删除临时表
drop table tblTempEACompany
drop table tblContact-----------------------------
经测试,是正确的
a.*
FROM
tblTempEACompany a
WHERE
NOT EXISTS(SELECT 1 FROM tblContact where ContactName=a.NTCLastName)
那么不管你有什么字符,中英混合,都应该没有问题
你的问题肯定不是中文字符,而是因为null的影响
你的资料是不是:SELECT ContactName FROM tblContact有出现null的资料,那么整个语句都会查询不出来,例子:
declare @T1 table(des varchar(10))
declare @T2 table(des varchar(10))insert into @T1 select 'aa' union all
select 'bb' insert into @T2 select 'aa' union all
select null --有个null纪录select * from @T1
where des not in (select des from @T2 )select * from @T1 T1
where not exists(select 1 from @T2 T2 where T2.des = T1.des)/*(2 row(s) affected)
(2 row(s) affected)des
---------- (0 row(s) affected)des
----------
bb(1 row(s) affected)
*/你换另外一种表达式来看看:
SELECT * FROM tblTempEACompany WHERE
not exists(select 1 from tblContact where ContactName = NTCLastName)
NTCLastName NOT EXISTS (SELECT * FROM tblContact WHERE ContactName = NTCLastName )