SQL IN语句-返回结果时保留重复项 [英] SQL IN statement - keep duplicates when returning results

问题描述

我有一个类似这样的sql查询:

I have an sql query something like this:

SELECT * FROM prices WHERE item IN ('item1', 'item2', 'item2', 'item3');

请注意重复值"item2". 我希望查询返回该项目的值的次数与它在IN子句中出现的次数相同.默认情况下，它仅返回一次.我该怎么办?

Notice the duplicate value 'item2'. I want the query to return the value for that item as many times as it appears in the IN clause. By default it only returns once. How can I do that?

推荐答案

然后您需要使用left join:

select p.*
from (select 'item1' as item union all select 'item2' union all
      select 'item2' union all select 'item3'
     ) i left join
     prices p
     on i.item = p.item;

这篇关于SQL IN语句-返回结果时保留重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！