Oracle添加顺序查询行，如果丢失

发布于 2025-02-03 08:56:03 字数 884 浏览 3 评论 0原文

在Oracle 19c中，我有一个数据，例如：

with t as (
select 1 Cat, 1 id, 11 val from dual
 union all
select 1, 3, 33 from dual
 union all
select 2, 2, 22 from dual
 union all
select 2, 4, 44 from dual)
select * 
  from t

在查询结果中，我想获得4每个cat带有IDS 1-4的行，如果没有这样的id cat a val必须为null：

cat	id	val
1	1	11
1	2
1	3	33
1	4 2 1
2	2
2	2	2 2 2 2
2 2 2	2 3
2	4	44 44

原文

In Oracle 19c I have a data like:

with t as (
select 1 Cat, 1 id, 11 val from dual
 union all
select 1, 3, 33 from dual
 union all
select 2, 2, 22 from dual
 union all
select 2, 4, 44 from dual)
select * 
  from t

In query result I want to get 4 rows per every cat with ids 1-4 and if there was no such id in that cat a val must be null:

cat	id	val
1	1	11
1	2
1	3	33
1	4
2	1
2	2	22
2	3
2	4	44

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北笙凉宸 2025-02-10 08:56:03

使用分区与行生成器加入：

SELECT t.cat,
       i.id,
       t.val
FROM   (SELECT LEVEL AS id FROM DUAL CONNECT BY LEVEL <= 4) i
       LEFT OUTER JOIN t
       PARTITION BY (t.cat)
       ON (i.id = t.id)

输出：

cat id val
1 1 11
1 2 null
1 3 33
1 4 null
2 1 null
2 2 22
2 3 null
2 4 44

db＆lt; >

Use a PARTITIONed join with a row-generator:

SELECT t.cat,
       i.id,
       t.val
FROM   (SELECT LEVEL AS id FROM DUAL CONNECT BY LEVEL <= 4) i
       LEFT OUTER JOIN t
       PARTITION BY (t.cat)
       ON (i.id = t.id)

Which outputs: