SQL Server：获取第一个联接值 [英] SQL Server: get the first joined value

问题描述

我的表如下所示：

ID  DEFINITION  VALUE
----------------------
1   A|B|C       1
2   A|All|All   3
3   A|B|All     6
4   All|B|All   4

该表后面的业务逻辑是3中最具体的定义传递字符串时应检索值。

The business logic behind that table is that the most specific DEFINITION of the 3 Values should be retrieved when passing a string. 'All' can be interpreted as '%'.

例如，全部可解释为％。如果我的定义是'X | B | Z'->值应为4。
如果我有'A | B | C'->值应为1（而不是1,3,6 ，4）-仅应从右开始检索最具体的结果。

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

'|'只是一个分隔符。每个子字符串可以是任何东西。在此示例中，我选择了A-C。不幸的是，它的所有内容都连接在一个列中。

The '|' is just a separator.Each of the sub strings could be anything. I have chosen A-C in this example. Unfortunately its all concatenated in one column.

我目前正在编写此查询，但是不幸的是，它的结果是所有匹配项：

I am currently writing this query but it results with all matches unfortunately:`

SELECT *
FROM T1
INNER JOIN T2 ON T1.ID = T2.ID
              AND (T2.DEFINITION = CASE 
                                      WHEN T2.DEFINITION = 'A|B|C'
                                         THEN T2.DEFINITION
                                      WHEN T2.DEFINITION = 'A|ALL|C'
                                         THEN T2.DEFINITION
                                      WHEN T2.DEFINITION = 'ALL|B|C'
                                         THEN T2.DEFINITION
                                      WHEN T2.DEFINITION = 'ALL|ALL|C'
                                         THEN T2.DEFINITION
                                      WHEN T2.DEFINITION = 'A|B|ALL'
                                         THEN T2.DEFINITION....
                                      ELSE 'All|All|All'
                                   END)

我想检索第一个匹配项而不是所有匹配项。该案例陈述的顺序已经在上面反映了我的业务逻辑。

I want to retrieve the first match of the case statement and not all of the matches. The order of that case statement is already reflects my business logic above.

有什么想法吗？

我担心解决方案非常简单，但现在看不到。

I fear the solution is very simple but I can't see it now.

推荐答案

似乎您可以执行以下操作：

It would seem that you could do something like this:

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) desc;

如果您试图对列中的值执行此操作，则可以使用应用，方法非常相似。

If you are trying to do this for values in a column, then you can use apply with a very similar approach.

这篇关于SQL Server：获取第一个联接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！