请帮忙看一下下列sql语句有什么错误谢谢
a表中有如下几条记录
MC BH
jc/m 1
kv/s 2
tm/ty 3
rt/b 4
ts/m 5
b表中字段
bh name
1 M
3 TS
我想查询a表中的MC中 不包有 b表中name字段下的记录
SELECT *
FROM a CROSS JOIN
b
WHERE (a.mc not LIKE '% ' + name + '% ')
这样不能查询出所想要的结果
[解决办法]
select a.* from a,b where a.BH = b.bh and charindex(b.name,a.MC)> 0
[解决办法]
select * from a where mc not in (select name from b)
[解决办法]
drop table a,b
go
create table a(MC varchar(10),BH int)
insert into a
select 'jc/m ',1
union all select 'kv/s ',2
union all select 'tm/ty ',3
union all select 'rt/b ',4
union all select 'ts/m ',5
create table b(bh int,name varchar(10))
insert into b
select 1, 'M '
union all select 3, 'TS '
select * from a
where not exists(select 1 from b where charindex(b.name,a.mc)> 0)
/*
MC BH
---------- -----------
kv/s 2
rt/b 4
（所影响的行数为 2 行）
*/
[解决办法]
select MC ,BH
from a
where left(MC,charindex( '/ ',MC)-1) not in (select name from b)
and right(MC,charindex( '/ ',reverse(MC))-1) not in (select name from b)
[解决办法]
Create Table a
(MCVarchar(10),
BH Int)
Insert a Select 'jc/m ', 1
Union All Select 'kv/s ', 2
Union All Select 'tm/ty ', 3
Union All Select 'rt/b ', 4
Union All Select 'ts/m ', 5
Create Table b
(bhInt,
name Varchar(10))
Insert b Select 1, 'M '
Union All Select 3, 'TS '
GO
SELECT a.* FROM a
Where NOT Exists(Select name From b Where '/ ' + a.mc + '/ ' Like '%/ ' + name + '/% ')
SELECT a.* FROM a
Where NOT Exists(Select name From b Where CharIndex( '/ ' + name + '/ ', '/ ' + a.mc + '/ ') > 0)
GO
Drop Table a, b
/*
MCBH
kv/s2
tm/ty3
rt/b4
*/
