根据业务逻辑获取第一个合并结果 [英] Get the first joined result based on business logic

问题描述

我的表T1看起来像这样：

My table T1 looks like this:

ID  VALUE
----------
1   1
2   32
3   6
4   42
5   8
6   62
7   43
8   34

我的第二个T2看起来像这样：

My second T2 looks like this:

ID  DEFINITION
---------------
1   A|B|C     
2   er|All|All|B  
3   All|All|All   
4   All|bela|All 
5   All|All|All|G
6   A|All|All
7   All|B|All 
8   Av|All|All|G

该表后面的业务逻辑是在传递字符串时应应用最具体的DEFINITION，并应检索相应的值。 全部可以解释为％。 |只是一个分隔符，用于定义您实际传递的值。每个字符串都可以是任何东西...

The business logic behind that table is that the most specific DEFINITION should be applied and the respective value should be retrieved when passing a string. 'All' can be interpreted as '%'. The '|' is just a separator which defines how many values you are actually passing. Each of the strings could be anything...

例如如果我的定义为'X | B | Z'->值应为7。如果我具有'A | B | C'->值应为1（而不是1,6,7）-仅最具体的结果应该从右边的休假水平开始检索。

E.g. if i have a definition which is 'X|B|Z' -> the Value should be 7. if i have'A|B|C' -> the Value should be 1 (not 1,6,7) - only the most specific result should be retrieved starting from the right which is the leave level.

我已经问了一个非常类似的问题（ SQL Server：获得第一个联接值），并获得了这一出色的解决方案Gordon Linnof，但不幸的是，它所涉及的范围不只是3个维度。我现在在该表中有了新的值，该值可以有更多的子字符串。

I have already asked a pretty similar Question(SQL Server: get the first joined value) and got this brilliant solution Gordon Linnof but unfortunately it does not cover more than 3 dimensions. I have now new Values in that table which could have more sub strings.

有没有办法动态地检索正确的值？

Is there any way to dynamically retrieve the right value?

select top (1) t.*
from t
where @str like replace(t.definition, 'All', '%')
order by (case when t.definition like '%All%All%All%' then 3
               when t.definition like '%All%All%' then 2
               when t.definition like '%All%' then 1
               else 0
          end) asc;

编辑：我需要为这个问题添加更多详细信息，因为Habo添加了一些有效的注释：stringvalues代表字母数字ID，例如egT34或ABC78，可以是varchar（255）。

I need to add more details to this question as Habo added some valid comments: the stringvalues represent alphanumeric id's e.g.T34 or ABC78 and can be varchar(255).

您所拥有的所有值越少越具体-这是正确的选择。 / p>

The less All values you have the more specific it is - which is the right one to choose.

推荐答案

这应该可以解决问题：

Declare @str VARCHAR(100) = 'A|B|C';

DECLARE
@Test TABLE
    (
         Id         INT
        ,Definition VARCHAR(100)
    )

INSERT INTO
    @Test (Id, Definition)
VALUES
     (1, 'A|B|C')
    ,(2, 'er|All|All|B')
    ,(3, 'All|All|All')
    ,(4, 'All|bela|All')
    ,(5, 'All|All|All|G')
    ,(6, 'A|All|All')
    ,(7, 'All|B|All')
    ,(8, 'Av|All|All|G')
;

---------------------------------------------------------------------------

WITH Tester AS
(
    SELECT
         Id
        ,Definition
        ,(LEN(Definition) - LEN(REPLACE(Definition, 'All', ''))) / LEN('All') AS numAlls
        ,REPLACE(Definition, 'All', '%') AS Mask
    FROM
        @Test
)

SELECT TOP 1
     Id
    ,Definition
FROM
    Tester
WHERE
    @str LIKE Mask
ORDER BY
     numAlls

这里的主键是列：

(LEN(Definition) - LEN(REPLACE(Definition, 'All', ''))) / LEN('All') AS numAlls

基本上，我认为您是通过排序给出的，并通过简单地计算单词 All 出现在定义中，而不是显式创建多个语句。

Basically, I'm taking the idea you gave with the ordering and simply making it more generic by simply counting the number of times the word All appears in the definition rather than explicitly creating multiple statements.

希望

编辑：

为了完整性并解决@HABO在注释中提出的真正有效的观点，即上述解决方案将在单词 All 出现时随时匹配在字符串中，即使它是另一个语句/单词的一部分，以下解决方案也更可靠/保证仅匹配 All 本身的情况：

For the sake of completeness and to address a truly valid point made by @HABO in the comments that the above solution would match any time the word All appeared in the string, even if it was part of another statement / word, the below solution is more robust / guarantees to only match cases where All is by itself:

WITH Tester AS
(
    SELECT
         Id
        ,Definition
        ,(LEN('|' + Definition + '|') - LEN(REPLACE('|' + Definition + '|', '|All|', ''))) / LEN('|All|') AS numAlls
        ,REPLACE('|' + Definition + '|', '|All|', '|%|') AS Mask
    FROM
        @Test
)

SELECT TOP 1
     Id
    ,Definition
FROM
    Tester
WHERE
    '|' + @str + '|' LIKE Mask
ORDER BY
     numAlls

基本上，您将e 定义和分隔符（ | ），以确保定义的每个部分以分隔符开始和结束（开始/结束值不是这种情况），然后我们可以搜索 | All | 。

Basically, you enclose the Definition with the separator character (|) to ensure every portion of the Definition begins and ends with the separator (which isn't the case for start / end values) then we can search for |All| throughout.

希望这是为什么它是一种更加健壮的解决方案。

Hope that makes sense as to why this is a substantially more robust solution.

这篇关于根据业务逻辑获取第一个合并结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！