枚举内部联接中的行 [英] Enumerating rows in a inner join
问题描述
我的桌子
id name num
1 a 3
2 b 4
我需要返回每行num次.我是这样的.
I need to return every row num number of times. I do it this way.
select DB.BAN_KEY as BAN_KEY, DB.CUST_FULLNAME as CUST_FULLNAME
from TST_DIM_BAN_SELECTED DB
inner join (select rownum rn from dual connect by level < 10) a
on a.rn <= DB.N
结果表如下所示.
id name
1 a
1 a
1 a
2 b
2 b
2 b
2 b
但是我还需要将组中的每一行都这样编号.
But I also need every row in the group to be numbered like this.
id name row_num
1 a 1
1 a 2
1 a 3
2 b 1
2 b 2
2 b 3
2 b 4
我该怎么办?
推荐答案
您不需要内部连接到虚拟表或使用解析函数来生成行号;您可以只在表本身上使用connect by(及其对应的level函数),就像这样:
You don't need an inner join to a dummy table or an analytic function to generate the row numbers; you could just use connect by (and its corresponding level function) on the table itself, like so:
WITH tst_dim_ban_selected AS (SELECT 1 ban_key, 'a' cust_fullname, 3 n FROM dual UNION ALL
SELECT 2 ban_key, 'b' cust_fullname, 4 n FROM dual)
-- end of mimicking your table with data in it. See SQL below
SELECT db.ban_key,
db.cust_fullname,
LEVEL row_num
FROM tst_dim_ban_selected db
CONNECT BY LEVEL <= db.n
AND PRIOR db.ban_key = db.ban_key -- assuming this is the primary key
AND PRIOR sys_guid() IS NOT NULL;
BAN_KEY CUST_FULLNAME ROW_NUM
---------- ------------- ----------
1 a 1
1 a 2
1 a 3
2 b 1
2 b 2
2 b 3
2 b 4
如果表的主键中除ban_key之外还有其他列,则需要确保它们包含在prior <column> = <column>
s的connect by子句列表中.因此,connect by可以唯一地标识每一行,这意味着它仅在该行上循环,而在其他行上则没有. PRIOR sys_guid() IS NOT NULL
是必需的,以防止发生by by循环.
If you have other columns than ban_key in the table's primary key, you need to make sure they are included in the connect by clause's list of prior <column> = <column>
s. This is so the connect by can identify each row uniquely, meaning that it's looping just over that row and no others. The PRIOR sys_guid() IS NOT NULL
is required to prevent connect by loops from occurring.
这篇关于枚举内部联接中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!