22,209
社区成员
发帖
与我相关
我的任务
分享
-- 使用 escape 关键字
create table test(id int, name varchar(20))
go
insert into test values
(1,'1234567890'),
(2,'123%567890'),
(3,'123_567890'),
(4,'123%%67890')
go
select * from test where name like '%\%%' escape '\'
go
drop table test
go
(4 行受影响)
id name
----------- --------------------
2 123%567890
4 123%%67890
(2 行受影响)
create table 价格表
(序号 int,颜色 varchar(10),价格 int)
insert into 价格表
select 1,'D*',200 union all
select 2,'H56',70 union all
select 3,'C?9',50
create table 物资表
(序号 int,名称 varchar(10),颜色 varchar(10))
insert into 物资表
select 1,'A','H56' union all
select 2,'B','D5' union all
select 3,'C','D12' union all
select 4,'D','C19' union all
select 5,'E','C29' union all
select 6,'F','C20' union all
select 7,'G','C229'
select a.序号,
a.名称,
a.颜色,
价格=b.价格
from 物资表 a
outer apply(select c.价格
from 价格表 c
where a.颜色 like replace(replace(c.颜色,'*','%'),'?','_')) b
/*
序号 名称 颜色 价格
----------- ---------- ---------- -----------
1 A H56 70
2 B D5 200
3 C D12 200
4 D C19 50
5 E C29 50
6 F C20 NULL
7 G C229 NULL
(7 row(s) affected)
*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#价格表') is null
drop table #价格表
Go
Create table #价格表([序号] int,[颜色] nvarchar(23),[价格] int)
Insert #价格表
select 1,N'D*',200 union all
select 2,N'H56',70 union all
select 3,N'C?9',50
Go
--> -->
if not object_id(N'Tempdb..#物资表') is null
drop table #物资表
Go
Create table #物资表([序号] int,[名称] nvarchar(21),[颜色] nvarchar(24))
Insert #物资表
select 1,N'A',N'H56' union all
select 2,N'B',N'D5' union all
select 3,N'C',N'D12' union all
select 4,N'D',N'C19' union all
select 5,N'E',N'C29' union all
select 6,N'F',N'C20' union all
select 7,N'G',N'C229'
Go
Select * from #物资表 AS a OUTER APPLY(SELECT TOP 1 [价格] FROM #价格表 WHERE a.[颜色] LIKE REPLACE(REPLACE([颜色],'?','_'),'*','%') ORDER BY [序号]) AS b
/*
序号 名称 颜色 价格
1 A H56 70
2 B D5 200
3 C D12 200
4 D C19 50
5 E C29 50
6 F C20 NULL
7 G C229 NULL
*/