使用类似的具有不同变量列表的查询表 [英] Query table with different list of variables using like
问题描述
我调用了一个网络服务，我得到了一个代码列表:A、B、C、D、E、F 等
I call a webservice and i get a list of codes: A, B, C, D, E, F etc.
在我的问题中，我得到了 A、B、C.
In my problem i get A, B, C.
有一个表格，您可以在其中使用上述代码创建集合，并且您可以为每个集合添加特定消息，以便最终用户可以理解代码.例如A表示ok"，B表示可以登录"因此，对于集合 A、B，消息可能是您可以登录".代码集保存在一列(代码)中.
There's a table where you can create sets with the above codes and for each set you can add a specific message so the end user can understand the codes. For example A means "ok", B means "you can log in" So for the set A, B a message could be "you can log in". The code sets are saved in one column (codes).
在我的问题中，我使用以下查询查询表:
In my problem i query the table by using the following query:
Select setid, codes, messagedescr from table1 where setid = (select max(setid) from table1
And codes Like '%A%'
And codes Like '%B%'
And codes Like '%C%');
此查询找到一行，但错误，codes"列包含以下代码:A、B、C、D.
This query finds a row but it's wrong, the column "codes" contains the following codes: A, B, C, D.
例如:
setid codes messagedescr
1 A, B, C, D You can login
2 B, C, D You can login for one day
3 A, C, E You can login but update your profile
4 B, C, E, F You cannot login
我不知道来自网络服务的代码的顺序，也不知道代码是如何保存在表格中的，所以我不得不无序地制作一些东西，这就是我使用 Likes 的原因.有25个代码:A、B、C等
I don't know the order of the codes from the webservice and i don't how the codes are saved in the table, so i had to make something without order, this why i used the Likes. There are 25 codes: A, B, C, etc
如何修复查询以便找到正确的消息?
How can i fix the query so i can find the correct message?
谢谢！
更新:感谢大家的回答，特别是需要额外工作(例如创建表格)的更详细的答案.该表可能有 10-20 行.一种情况是为代码创建另一个表并将其与设置的 id 连接起来.或者另一种情况是计算服务中的代码长度，看看它们是否与表中的长度匹配.
Update: Thank you all for your answers and specially the more detailed answers that had extra work such as creating the tables. The table might have from 10-20 rows. One case is to create another table for the codes and join it with the set ids. Or the other case is to count the lengths of the codes from the service and see if they match with the length in the table.
推荐答案
Oracle 设置:
从一个简单的函数开始将一个分隔的字符串分割成一个集合:
Start with a simple function to split a delimited string into a collection:
CREATE OR REPLACE TYPE stringlist AS TABLE OF VARCHAR2(20)
/
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
p_result stringlist := stringlist();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
和一些示例数据:
CREATE TABLE your_table( setid, codes, messagedescr )
SELECT 1, 'A,B,C,D', 'You can login' FROM DUAL UNION ALL
SELECT 2, 'B,C,D', 'You can login for one day' FROM DUAL UNION ALL
SELECT 3, 'A,C,E', 'You can login but update your profile' FROM DUAL UNION ALL
SELECT 4, 'B,C,E,F', 'You cannot login' FROM DUAL;
然后你可以做(传递你的输入 C,A,B - 以任何顺序 - 作为绑定参数 :your_code):
Then you can do (passing your input C,A,B - in any order - as the bind parameter :your_code):
SELECT *
FROM (
SELECT *
FROM your_table
WHERE split_string( codes ) SUBMULTISET OF split_String( :your_code )
ORDER BY setid DESC
)
WHERE ROWNUM = 1;
并且它将输出具有最高匹配代码集的行.
and it will output the row with the highest matching set of codes.
注意: 上面的示例假设您需要将表中的所有代码与输入字符串中的代码相匹配.如果您只需要匹配至少一个，那么您可以使用:
Note: The above example assumes that you need to match all the codes in the table to codes in your input string. If you only need to match at least one then you can use:
WHERE split_string( codes ) MULTISET INTERSECT split_String( :your_code ) IS NOT EMPTY
这篇关于使用类似的具有不同变量列表的查询表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
