英文:
Make an SQL query return duplicate rows without using the UNION ALL close
问题
我有一个使用情况，即使多次指定相同的 id，也需要返回所有记录。
select d.id from data d where d.id in (1420552, 1420553)
union all
select d.id from data d where d.id in (1420552, 1420553)
由于 "IN" 谓词只是将所有指定的值翻译为 "OR"，这不会给我想要的结果。
select d.id from data d where d.id in (1420552, 1420553, 1420552, 1420553)
因此，我想知道是否有任何替代方法来获得所需的结果。提前谢谢。
英文:
I have a use case that imply returning all the records even if the same id is specified many times
select d.id from data d where d.id in (1420552, 1420553)
union all
select d.id from data d where d.id in (1420552, 1420553)
As the "IN" predicate is just translated as an "OR" between all the specified values it will not give me the desired result.
select d.id from data d where d.id in (1420552, 1420553, 1420552, 1420553)
Therefore i'm wondering if there is any alternative to get the desired result.
Thank you in advance.
答案1
得分: 1
非常奇怪的用例...
但是假设您的平台和 Db2 版本支持表值构造函数...
请尝试：
with cte(id)
as (values (1420552),(1420553)
,(1420552),(1420553) )
select d.id
from data d
join cte c on d.id = c.id;
英文:
Very weird use case...
But assuming your platform and version of Db2 supports table value constructor...
Try
with cte(id)
as (values (1420552),(1420553)
,(1420552),(1420553) )
select d.id
from data d
join cte c on d.id = c.id;
答案2
得分: 0
我可能会使用 cross join：
select d.id
from data d cross join
(select 1 from sysibm.sysdummy1 union all
select 2 from sysibm.sysdummy1 union all
) n
where d.id in (1420552,1420553);
或者更简洁地写作：
select d.id
from data d cross join
(values (1), (2)) v(n)
where d.id in (1420552,1420553);
英文:
I would probably use a cross join:
select d.id
from data d cross join
(select 1 from sysibm.sysdummy1 union all
select 2 from sysibm.sysdummy1 union all
) n
where d.id in (1420552,1420553);
Or more concisely as:
select d.id
from data d cross join
(values (1), (2)) v(n)
where d.id in (1420552,1420553);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论