問題描述

這是此問題的后續問題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

請參閱演示.

這篇關于TSQL 返回存在的內容的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！