TSQL 返回存在的内容

问题描述

这是此问题的后续问题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

请参阅演示.