强制MySQL从WHERE IN子句中返回重复项，而不使用JOIN / UNION？ [英] Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

问题描述

这可能不是很明智，但是如果在 WHERE IN 子句中有重复条件，我想让MySQL返回确切的重复行。这是可能的吗？

举个例子：

  SELECT 
列
从
表
 WHERE 
 id IN（1，2，3，4，5，1，2，5，5）
  

我像MySQL一样返回我的id为3次的行，id为1和2两次，3和4为一次。 / p>

作为 IN 参数的长度，以及重复计数（一次，两次，三次等） 。），将是任意的，我不想依赖 UNION 或 JOIN 。是否可以这样做？

解决方案

我不知道为什么你要禁止JOIN作为SQL相当重要的。这就像在功能语言中禁止函数调用。

解决这个问题的一个很好的方法是创建一个包含要返回的id并将其加入的结果集。这是一种方法：

  SELECT Table1。* 
 FROM Table1 
 JOIN（SELECT 1 AS id 
 UNION ALL SELECT 2 
 UNION ALL SELECT 3 
 UNION ALL SELECT 4 
 UNION ALL SELECT 5 
 UNION ALL SELECT 1 
 UNION ALL SELECT 2 
 UNION ALL SELECT 5 
 UNION ALL SELECT 5）AS T1 
 ON Table1.id = T1.id 
  

我不知道你是否考虑过这种方法？它没有任何你似乎害怕的问题。

如果你禁止加入，你不能这样做，除非你使用存储过程，我会说比加入更糟糕。

This might not be very sensible, but I'ld like to let MySQL return me the exact duplicate rows if there are duplicate criteria in the WHERE IN clause. Is this possible?

Take this example:

SELECT
   columns
FROM
   table
WHERE
   id IN( 1, 2, 3, 4, 5, 1, 2, 5, 5)

I'ld like MySQL to return me rows with id 5 three times, id's 1 and 2 twice, and 3 and 4 once.

As the lenght of the IN arguments, as well as the duplicate count (once, twice, three times, etc.), will be arbitrary I don't want to rely on UNION or JOIN. Is something like this possible otherwise?

解决方案

I'm not sure why you want to ban JOIN as its fairly essential to SQL. It's like banning function calls in a functional language.

A good way to solve this is to create a result set containing the ids you want to return and join with it. Here's one way to do it:

SELECT Table1.*
FROM Table1
JOIN (SELECT 1 AS id
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 5
      UNION ALL SELECT 5) AS T1
ON Table1.id = T1.id

I'm not sure if you have considered this method? It has none of the problems that you seem to be afraid of.

If you ban joins you can't do this unless you use a stored procedure, which I'd say is worse than joining.

这篇关于强制MySQL从WHERE IN子句中返回重复项，而不使用JOIN / UNION？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！