返回所有IN（1,2,3,3,3,1）子句，其中IN条件重复 [英] Return all for where IN (1,2,3,3,3,1) clause with duplicates in the IN condition

问题描述

我需要返回以下所有值：select ... where IN（1,2,3,3,3,1）

I need to return all values for: select...where IN (1,2,3,3,3,1)

我有一个表，其中唯一ID。我有以下查询：

I have a table with unique IDs. I have the following query:

Select * From Table_1 Where ID IN (1,2,3,3,3,1);

到目前为止，我只返回3条唯一值（1,2,3）的记录

So far I'm returning only 3 records for unique values (1,2,3)

我想返回6条记录。

我需要获取图片上显示的结果集。

I need to get result set shown on the picture.

推荐答案

您不能使用 IN 条件执行此操作，因为 IN 将您的商品视为一组（即，确保唯一性）

You cannot do this using the IN condition, because IN treats your items as a set (i.e. ensures uniqueness).

您可以通过加入 UNION ALL 来产生所需的结果，如下所示：

You can produce the desired result by joining to a UNION ALL, like this:

SELECT t.*
FROM Table_1 t
JOIN ( -- This is your "IN" list
          SELECT 1 AS ID
UNION ALL SELECT 2 AS ID
UNION ALL SELECT 3 AS ID
UNION ALL SELECT 3 AS ID
UNION ALL SELECT 3 AS ID
UNION ALL SELECT 1 AS ID
) x ON x.ID = t.ID

这篇关于返回所有IN（1,2,3,3,3,1）子句，其中IN条件重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！