SQL Server: get the first joined value

问题:

My table looks like this:

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

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 '%'.

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.

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.

Any ideas?

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

回答1:

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.

回答2:

like this?

CREATE TABLE #TestTable
    ([ID] int, [DEFINITION] varchar(9), [VALUE] int)
;

INSERT INTO #TestTable
    ([ID], [DEFINITION], [VALUE])
VALUES
    (1, 'A|B|C', 1),
    (2, 'A|All|All', 3),
    (3, 'A|B|All', 6),
    (4, 'All|B|All', 4)
;

select [ID], [DEFINITION], [VALUE] from (
    SELECT T1.*, row_number() over (order by CASE 
                    WHEN T1.DEFINITION = 'A|B|C'
                        THEN 1
                    WHEN T1.DEFINITION = 'A|ALL|C'
                        THEN 2
                    WHEN T1.DEFINITION = 'ALL|B|C'
                        THEN 3
                    WHEN T1.DEFINITION = 'ALL|ALL|C'
                        THEN 4
                    WHEN T1.DEFINITION = 'A|B|ALL'
                        THEN 5
                    ELSE 6
                    END
                ) rnk
    FROM #TestTable T1
)T where rnk = 1

---

Result :

ID          DEFINITION VALUE
----------- ---------- -----------
1           A|B|C      1