問(wèn)題描述

一個(gè)表包含一個(gè) Title 字段和一個(gè) Tags 字段.標(biāo)簽是通過(guò)潛在狄利克雷分配(LDA)從文檔中生成的，可以是例如魚(yú)、烤箱、時(shí)間"、燒烤、啤酒"或肉、燒烤".標(biāo)簽的長(zhǎng)度不固定.

A table contains a Title field and a Tags field. The tags are generated via latent Dirichlet allocation (LDA) from documents and can be e.g. 'fish, oven, time', 'BBQ, beer' or ' meat, BBQ'. The length of the tags is not fixed.

給定一組標(biāo)簽，無(wú)論標(biāo)簽的順序如何，如何找到匹配的標(biāo)簽數(shù)量最多的記錄?

Given a set of tags, how to find the record with the maximum amount of tags matching no matter the order of the tags?

因此，如果給出BBQ, meat"，最好的結(jié)果應(yīng)該是meat, BBQ".如果給出'BBQ,fish, cream'，則可以返回所有三個(gè)記錄(它們都有一個(gè)匹配的標(biāo)簽).

So, if 'BBQ, meat' is given the best result should be 'meat, BBQ'. If 'BBQ, fish, cream' is given all three records can be returned (they all have one matching tag).

推薦答案

使用這個(gè)函數(shù)并創(chuàng)建這個(gè)>

Use this function and Create this one

    CREATE FUNCTION dbo.getCountOfMatch ( @mainString VARCHAR(MAX),  @searchString nvarchar(max))
    RETURNS
      INT
    AS
    BEGIN
        DECLARE @returnCount INT

        SELECT 
            @returnCount = COUNT(1)
        FROM 
            splitstring(@mainString) A INNER JOIN 
            splitstring(@searchString) B ON A.Name = B.Name

        RETURN @returnCount
    END

和

    SELECT TOP 1 // What you want
      Title,
      Tags
    FROM
    (
        SELECT
            A.Title,
            A.Tags,
            dbo.getCountOfMatch(A.Tags, @search) CountTags -- The number of matches.
        FROM
            TABLE A
    ) B
    ORDER BY B.CountTags DESC

更新

DECLARE @searchText NVARCHAR(MAX) = 'BBQ, meat'
DECLARE @query NVARCHAR(MAX) = '
        SELECT
            *
        FROM
            Table
        WHERE '

SELECT
    @query += 
    (
        SELECT
            'Tags like ''%' + A.Name + '%'' AND ' -- Dont forget trim!
        FROM 
            splitstring(@searchText) A
        FOR XML PATH ('')
    )

SELECT @query = LEFT(@query, LEN(@query) - 4) + 'ORDER BY LEN(Tags)' -- For exactly matching: LEN(Tags) = LEN(@searchText)

EXEC sp_executesql  @query

查詢的樣子；

    SELECT
        *
    FROM
        Table
    WHERE 
        Tags like '%BBQ%' AND 
        Tags like '%meat%'
    ORDER BY LEN(Tags) 

這篇關(guān)于TSQL 匹配盡可能多的逗號(hào)分隔標(biāo)簽的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！