强制 MySQL 从 WHERE IN 子句返回重复项而不使用 JOIN/UNION?

2022-01-10 00:00:00 duplicates mysql where-in

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

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?

举个例子:

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

我希望 MySQL 向我返回 id 为 5 的行 3 次，id 为 1 和 2 的行两次，以及 3 和 4 的一次.

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

由于 IN 参数的长度以及重复计数(一次、两次、三次等)将是任意的，我不想依赖 UNION 或 JOIN.否则这样的事情可能吗?

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?

推荐答案

我不知道你为什么要禁止 JOIN，因为它对 SQL 相当重要.这就像禁止函数式语言中的函数调用.

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.

解决此问题的一个好方法是创建一个结果集，其中包含您要返回的 ID 并加入其中.这是一种方法:

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.

相关文章