如何在SQL中获取IN运算符的所有匹配文本？

To be clear, here is the scenario: I am executing a query like the following:

要清楚,这是场景:我正在执行如下查询:

SELECT * FROM table_name WHERE uid IN ( 1, 3, 5, 8, 23, 4, 589 ... )

SELECT * FROM table_name WHERE uid IN(1,3,5,8,23,4,589 ...)

The list of uids is coming from the user.

uid列表来自用户。

Is there a way to get all matching uids in the list using SQL?

有没有办法使用SQL获取列表中的所有匹配uid?

I am looking for Oracle SQL syntax but I also want to know the syntax for common database system.

我正在寻找Oracle SQL语法,但我也想知道常见数据库系统的语法。

Thanks.

UPDATE

I want to get ALL MATCHING uids. For example, in the table there is no record that has a uid 8 and 23, the output should be 1, 3, 5, 4, 589 ...

我想得到所有匹配的uids。例如,在表中没有具有uid 8和23的记录,输出应该是1,3,5,4,589 ......

2 个解决方案

#1

Is there a way to get all matching uids in the list using SQL?

有没有办法使用SQL获取列表中的所有匹配uid?

Yes, use your query but just get the uid column. If you do not want repeats then use the DISTINCT keyword:

是的,使用您的查询,但只需获取uid列。如果您不想重复,请使用DISTINCT关键字:

SELECT DISTINCT uid
FROM   table_name
WHERE  uid IN ( 1, 3, 5, 8, 23, 4, 589 ... )

If you want it in a comma delimited list:

如果你想要它在逗号分隔列表中:

SELECT LISTAGG( uid, ',' ) WITHIN GROUP ( ORDER BY uid ) AS uids
FROM   table_name
WHERE  uid IN ( 1, 3, 5, 8, 23, 4, 589 ... )

#2

I am note sure I understood correctly your question. If you want to know, given a list of values, what are the one in the table and what are not, you can try this; you need to answer the values you are looking for in a temp table (or a select union clause or something like that) and then do a left join with your table (TX here is your table). THIS SCRIPT IS FOR MSSQL TO GIVE YOU THE IDEA. IT SHOULD BE EASY TO CONVERT THEM FOR ORACLE, IF THIS SOLUTION IS WHAT YOU WHERE ASKING (IT'S WAS NOT CLEAR FROM YOUR QUESTION).

我注意到我确实理解你的问题。如果你想知道,给定一个值列表,表中的那个是什么,什么不是,你可以试试这个;你需要在临时表(或者选择联合子句或类似的东西)中回答你正在寻找的值,然后用你的表做左连接(这里的TX就是你的表)。这篇文章是为了MSSQL给你的想法。如果这个解决方案是你要求的东西(它不是从你的问题中清除),那么它应该很容易为ORACLE转换它们。

CREATE TABLE TX (UID INT)
INSERT INTO TX VALUES (1),(5),(4)

CREATE TABLE TEMP1 (ID_VAL INT)
INSERT INTO TEMP1 SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 23 UNION ALL SELECT 4 UNION ALL SELECT 589 

SELECT * FROM TEMP1

SELECT * 
FROM TEMP1
LEFT JOIN TX ON TEMP1.ID_VAL = TX.UID

Output:

ID_VAL      UID
----------- -----------
1           1
3           NULL
5           5
8           NULL
23          NULL
4           4
589         NULL

#1