22,207
社区成员
发帖
与我相关
我的任务
分享
--材料表
DECLARE @t TABLE (
id INT IDENTITY(1,1) PRIMARY KEY,
mat NVARCHAR(50)
)
INSERT INTO @t VALUES ('abcd')
INSERT INTO @t VALUES ('bcd')
INSERT INTO @t VALUES ('abd')
INSERT INTO @t VALUES ('ae')
INSERT INTO @t VALUES ('bec')
INSERT INTO @t VALUES ('fcab')
--关键字表
DECLARE @k TABLE (
keyword NVARCHAR(20)
)
INSERT INTO @k VALUES ('ab')
INSERT INTO @k VALUES ('bc')
--结果表
DECLARE @r TABLE (
id INT PRIMARY KEY,
mat NVARCHAR(50)
)
DECLARE @i INT,@imax INT
SELECT @i=MIN(id),@imax=MAX(id) FROM @t
WHILE @i<=@imax
BEGIN
INSERT INTO @r (id,mat)
SELECT a.id,a.mat FROM @t AS a WHERE a.id=@i AND not EXISTS(
SELECT 1 FROM @k AS b WHERE b.keyword>'' AND a.mat LIKE '%'+b.keyword+'%'
)
SET @i=@i+1
END
SELECT * FROM @r
/*
id mat
4 ae
5 bec
*/
use Tempdb
go
--> --> 听雨停了-->生成测试数据
--原材料表
if not object_id(N'Tempdb..#tabA') is null
drop table #tabA
Go
Create table #tabA([原材料] nvarchar(24))
Insert #tabA
select N'美国香蕉' union all
select N'云南香蕉' union all
select N'北京鸭梨' union all
select N'云南米线' union all
select N'广东大米'
Go
--关键词表
if not object_id(N'Tempdb..#tabB') is null
drop table #tabB
Go
Create table #tabB([关键词] nvarchar(22))
Insert #tabB
select N'香蕉' union all
select N'大米'
Go
--测试数据结束
SELECT * FROM #tabA a
WHERE NOT EXISTS (
SELECT a.* FROM #tabB b
WHERE CHARINDEX(b.关键词,a.原材料)>0
)
DECLARE @t TABLE (
mat NVARCHAR(50),
keyword NVARCHAR(20)
)
INSERT INTO @t VALUES ('abcd','ab')
INSERT INTO @t VALUES ('bcd','bc')
INSERT INTO @t VALUES ('abd','')
INSERT INTO @t VALUES ('ae','')
INSERT INTO @t VALUES ('bec','')
INSERT INTO @t VALUES ('fcab','')
SELECT * FROM @t AS a WHERE not EXISTS(
SELECT 1 FROM @t AS b WHERE b.keyword>'' AND a.mat LIKE '%'+b.keyword+'%'
)
/*
mat keyword
ae
bec
*/
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([原材料] nvarchar(25))
Insert #T1
select N'粉状增合剂' union all
select N'氨基磺酸'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([关键词] nvarchar(22))
Insert #T2
select N'粉状'
Go
--测试数据结束
SELECT *
FROM #T1
WHERE NOT EXISTS ( SELECT *
FROM #T2
WHERE 原材料 LIKE '%' + 关键词 + '%' )