时间戳

首页 > 解决方案 > 返回在同一列或不同列中找到多个关键字的行，以显示这些关键字以及受尊重的列

sql - 返回在同一列或不同列中找到多个关键字的行，以显示这些关键字以及受尊重的列

问题描述

我希望有人根据以下示例表对以下内容提出快速建议：

|Column1 |Column2 |Column3 |Column4 |
|--------|--------|--------|--------|
|ABC XYZ |DFG KIL |YUI XYZ |ABC IOH |
|YDT NJK |ABC HJK |NJM XYZ |WEC OPP |

我正在考虑能够构建一个查询来搜索同一列或不同列中的多个单词，以显示这些关键字和受尊重的列。这一点，无需依靠光标。

即，如果用户搜索关键字，我想要以下输出'ABC', 'YUI', 'OPP'：

|Col1    |Col2    |Col3    |Col4    |Found Keyword|Keyword Found in |
|--------|--------|--------|--------|-------------|-----------------|
|ABC XYZ |DFG KIL |YUI XYZ |ABC IOH |ABC, YUI     |Col1,Col3,Col4   | 
|YDT NJK |ABC HJK |NJM XYZ |WEC OPP |ABC, OPP     |Col2,Col4        |

我一直在尝试做各种事情，例如(sys.columns, UNION ALL, CROSS JOIN)，但到目前为止收效甚微。

有超过 100K 行，最好的方法是什么？

注意：我使用的是 SQL Server 2012 版本。

标签： sqlsql-server

解决方案

已经有人评论说您的设计应该重新设计。

现在，这是一个应该响应您的用例的查询。这通过声明您在子查询中搜索的单词（这使得添加更多内容非常容易），然后JOIN使用表来工作：基本上，检查所有连接的列是否与搜索的值匹配（假设空间分隔器）。

然后，外部查询使用STRING_AGG()（在 SQL Server 2017 中可用）执行聚合作业。生成匹配列列表的部分有点棘手，因为需要为每条记录手动生成 CSV 值。

注意：如果您有一个唯一的列（或列的组合）以避免在聚合时混合记录，那将是很好的。我假设一个主键列名为id.

询问：

SELECT
    t.Col1,
    t.Col2,
    t.Col3,
    t.Col4,
    STRING_AGG(words.txt, ',') FoundKeywords,
    STRING_AGG(
        STUFF(CONCAT( 
            IIF(CONCAT(' ', t.Col1, ' ') LIKE CONCAT(' %', words.txt, '% '), ',Col1', ''),
            IIF(CONCAT(' ', t.Col2, ' ') LIKE CONCAT(' %', words.txt, '% '), ',Col2', ''),
            IIF(CONCAT(' ', t.Col3, ' ') LIKE CONCAT(' %', words.txt, '% '), ',Col3', ''),
            IIF(CONCAT(' ', t.Col4, ' ') LIKE CONCAT(' %', words.txt, '% '), ',Col4', '')
        ), 1, 1, ''),
        ','
    ) KeywordFoundin

FROM
    mytable t
    INNER JOIN (
        SELECT 'ABC' txt UNION ALL SELECT 'YUI' UNION ALL SELECT 'OOP'
    ) words
        ON CONCAT(' ', t.Col1, ' ', t.Col2, ' ', t.Col3, ' ', t.col4, ' ') 
            LIKE CONCAT(' %', words.txt, '% ')
GROUP BY 
    t.id,
    t.Col1,
    t.Col2,
    t.Col3,
    t.Col4

DB Fiddle 上的演示：

Col1 | Col2 | Col3 | Col4 | 找到关键字 | 关键字Foundin
:-------- | :-------- | :-------- | :-------- | :------------ | :-------------
ABC XYZ | DFG 基尔 | YUI XYZ | ABC IOH | ABC,YUI | Col1,Col4,Col3
YDT NJK | ABC HJK | 新泽西州 XYZ | WEC OPP | 美国广播公司 | Col2

在 SQL Server < 2017 中， whereSTRING_AGG()不可用，我会使用一个简单SELECT的 with（非常长）表达式来比较和连接所需的信息：

SELECT
    Col1,
    Col2,
    Col3,
    Col4,
    STUFF(CONCAT(
        IIF(CONCAT(' ', Col1, ' ', Col2, ' ', Col3, ' ', Col4, ' ') LIKE CONCAT(' %', 'ABC', '% '), ',ABC', ''),
        IIF(CONCAT(' ', Col1, ' ', Col2, ' ', Col3, ' ', Col4, ' ') LIKE CONCAT(' %', 'YUI', '% '), ',YUI', ''),
        IIF(CONCAT(' ', Col1, ' ', Col2, ' ', Col3, ' ', Col4, ' ') LIKE CONCAT(' %', 'OOP', '% '), ',OOP', '')
    ), 1, 1, '') FoundKeywords,
    STUFF(CONCAT( 
        IIF(
            CONCAT(' ', Col1, ' ') LIKE CONCAT(' %', 'ABC', '% ')
                OR CONCAT(' ', Col1, ' ') LIKE CONCAT(' %', 'YUI', '% ')
                OR CONCAT(' ', Col1, ' ') LIKE CONCAT(' %', 'OOP', '% '),
            ',Col1', 
            ''
        ),
        IIF(
            CONCAT(' ', Col2, ' ') LIKE CONCAT(' %', 'ABC', '% ')
                OR CONCAT(' ', Col2, ' ') LIKE CONCAT(' %', 'YUI', '% ')
                OR CONCAT(' ', Col2, ' ') LIKE CONCAT(' %', 'OOP', '% '),
            ',Col2', 
            ''
        ),
        IIF(
            CONCAT(' ', Col3, ' ') LIKE CONCAT(' %', 'ABC', '% ')
                OR CONCAT(' ', Col3, ' ') LIKE CONCAT(' %', 'YUI', '% ')
                OR CONCAT(' ', Col3, ' ') LIKE CONCAT(' %', 'OOP', '% '),
            ',Col3', 
            ''
        ),
        IIF(
            CONCAT(' ', Col4, ' ') LIKE CONCAT(' %', 'ABC', '% ')
                OR CONCAT(' ', Col4, ' ') LIKE CONCAT(' %', 'YUI', '% ')
                OR CONCAT(' ', Col4, ' ') LIKE CONCAT(' %', 'OOP', '% '),
            ',Col4', 
            ''
        )
    ), 1, 1, '') KeywordFoundin
FROM mytable 
WHERE
    CONCAT(' ', Col1, ' ', Col2, ' ', Col3, ' ', col4, ' ') LIKE CONCAT(' %', 'ABC', '% ')
    OR CONCAT(' ', Col1, ' ', Col2, ' ', Col3, ' ', col4, ' ') LIKE CONCAT(' %', 'YUI', '% ')
    OR CONCAT(' ', Col1, ' ', Col2, ' ', Col3, ' ', col4, ' ') LIKE CONCAT(' %', 'OOP', '% ')

DB Fiddle 上的演示

推荐阅读