有这样两个表：
表A
id soft_name company
－－－－－－－－－－－－－－－－－－－－－－
A office 2000 microsoft
A mcafee agent mcafee
A vnc33 vnc
A qq2007 qq
A game1 game1
A game2 game2
B office 2003 microsoft
B vnc4.0 vnc
B mcafee8i mcafee
B microsoft microsoft
B game3 game3
表B
id name
－－－－－－－－－－
1 office
2 mcafee
3 vnc
4 microsoft
现在要用一条SQL语句，从表A的soft_name中将含有表B中name（模糊匹配）的排除，得到如下结果：
id soft_name company
－－－－－－－－－－－－－－－－－－
A qq2007 qq
A game1 game1
A game2 game2
B game3 game3
help!!
------解决方案--------------------
- SQL code
--原始数据：@Adeclare @A table(id varchar(1),soft_name varchar(12),company varchar(9))insert @Aselect 'A','office 2000','microsoft' union allselect 'A','mcafee agent','mcafee' union allselect 'A','vnc33','vnc' union allselect 'A','qq2007','qq' union allselect 'A','game1','game1' union allselect 'A','game2','game2' union allselect 'B','office 2003','microsoft' union allselect 'B','vnc4.0','vnc' union allselect 'B','mcafee8i','mcafee' union allselect 'B','microsoft','microsoft' union allselect 'B','game3','game3'--原始数据：@Bdeclare @B table(id int,name varchar(9))insert @Bselect 1,'office' union allselect 2,'mcafee' union allselect 3,'vnc' union allselect 4,'microsoft'select * from @A a where not exists (select 1 from @B where charindex(name,a.soft_name)>0)/*id soft_name company ---- ------------ --------- A qq2007 qqA game1 game1A game2 game2B game3 game3*/
------解决方案--------------------
- SQL code
declare @a table(id varchar(2), soft_name varchar(20) , company varchar(20))insert @a select 'A', 'office 2000', 'microsoft' union all select 'A', 'mcafee agent', 'mcafee' union all select 'A', 'vnc33', 'vnc' union all select 'A', 'qq2007', 'qq' union all select 'A', 'game1', 'game1' union all select 'A', 'game2', 'game2' union all select 'B', 'office 2003', 'microsoft' union all select 'B', 'vnc4.0', 'vnc' union all select 'B', 'mcafee8i', 'mcafee' union all select 'B', 'microsoft', 'microsoft' union all select 'B', 'game3', 'game3' declare @b table(id int, name nvarchar(20))insert @b select 1, 'office' insert @b select 2, 'mcafee' insert @b select 3, 'vnc' insert @b select 4, 'microsoft'--或用patindex/charindex select a.* from @a a where not exists(select 1 from @b where patindex( '%'+Name+'%',a.soft_name)>0 ) id soft_name company ---- -------------------- -------------------- A qq2007 qqA game1 game1A game2 game2B game3 game3
------解决方案--------------------
select a.* from @a a where not exists(select 1 from @b where a.soft_name like '%'+Name+'%' )