三个表的数据如下:
表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 结果:
id soft_name company
------------------
A qq2007 qq
A game1 game1
A game2 game2
B game3 game3
表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 结果:
id soft_name company
------------------
A qq2007 qq
A game1 game1
A game2 game2
B game3 game3
解决方案 »
- 救命啊高手!困扰我很久的问题!
- 删除重复vipid并且point字段积分小的删除掉。
- 如何快速执行多条Sql语句(如insert,update)
- sql数据导出表时,字段的默认时会改变!
- 在一台机器上生成了DTS的.dts文件(可以正常运行),请问,如何把这个包安装到另一个SQL SERVER中,并存在SQL SERVER中?
- 在拼接字符串中如何获取sum的列
- 关于插入数据的问题
- 急!如何使2004-01-31 12:12:00两头不用加单引号插入datetime段中??谢谢
- 求一个同期销售比较SQL语句.
- SQL Server7.0如何连上SQL Server6.5?高手快来!!分多多!!!
- 有关存储过程问题?请大家帮忙。关于显示控制的,新增加一个字段,让他置顶。
- 备分还原问题
declare @A table(id varchar(1),soft_name varchar(12),company varchar(9))
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'
--原始数据:@B
declare @B table(id int,name varchar(9))
insert @B
select 1,'office' union all
select 2,'mcafee' union all
select 3,'vnc' union all
select 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 qq
A game1 game1
A game2 game2
B game3 game3
*/
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 qq
A game1 game1
A game2 game2
B game3 game3
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 a.soft_name like Name+'%' )
id soft_name company
---- -------------------- --------------------
A qq2007 qq
A game1 game1
A game2 game2
B game3 game3(所影响的行数为 4 行)