问题描述
这是此问题的后续问题TSQL 搜索文本
我的情况如下
declare @vendor as table (vName varchar(max)) insert into @vendor select * from ( values ('Maccro'), ('Accro') ) t (one) declare @transaction as table (descr varchar(max)) insert into @transaction select * from ( values ('recl Maccro something'), ('lrec Accro Maccro'), ('lrec Maccr0'), ('Maccro indeed'), ('ACCR Accro'), ('Raac else') ) t (one)
我可以运行以下查询
Select a.descr, CASE WHEN EXISTS(select * From @vendor b where Charindex(b.Vname, a.descr) > 0) then 1 else 0 End [doesContain] from @transaction a
返回以下内容
| descr | doesContain | | --------------------- | ----------- | | recl Maccro something | 1 | | lrec Accro Maccro | 1 | | lrec Maccr0 | 0 | | Maccro indeed | 1 | | ACCR Accro | 1 | | Raac else | 0 |
但是是否有可能看到 doesContain 返回 1 的值,即 @vendor 表中的值,Exists 返回一场比赛.
But is it possible to see on what value it returns 1 for doesContain, i.e. on what value from @vendor table, Exists returns a match.
我想要的输出如下
| descr | doesContain | containsWhat | | --------------------- | ----------- | ------------ | | recl Maccro something | 1 | Maccro | | lrec Accro Maccro | 1 | Accro | | lrec Maccr0 | 0 | | | Maccro indeed | 1 | Maccro | | ACCR Accro | 1 | Accro | | Raac else | 0 | |
推荐答案
使用 @transaction 的 LEFT 连接到 @vendor 和 FIRST_VALUE() 获取第一次出现的窗口函数:
Use a LEFT join of @transaction to @vendor and FIRST_VALUE() window function to get the 1st occurrence:
Select distinct a.descr, CASE WHEN b.Vname is not null then 1 else 0 End [doesContain], first_value(b.Vname) over (partition by a.descr order by Charindex(b.Vname, a.descr)) containsWhat from @transaction a left join @vendor b on Charindex(b.Vname, a.descr) > 0
请参阅演示.